

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/AppGetDBDatabaseList.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
--
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/AppGetDBDatabaseList.sp,v $ $Id: AppGetDBDatabaseList.sp,v 1.1.2.51.4.1 2021/07/08 20:11:05 stelagamsetti Exp $";
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='AppGetDBDatabaseList')
BEGIN
	print '>>> Drop Stored Procedure: AppGetDBDatabaseList <<<'
	drop procedure AppGetDBDatabaseList
END
IF EXISTS (select * from GxQscripts where name='AppGetDBDatabaseList')
	delete from GxQscripts where name = 'AppGetDBDatabaseList'
GO

IF EXISTS (select * from GXDBVersions where aliasname='AppGetDBDatabaseList')
	delete from GXDBVersions where aliasname = 'AppGetDBDatabaseList'
GO
print '... Creating Procedure: AppGetDBDatabaseList'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure AppGetDBDatabaseList
  @i_xmlString XML
AS
  DECLARE @o_xmlString XML;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY
	IF OBJECT_ID('tempdb.dbo.#getIdaObjects') IS NOT NULL
		DROP TABLE #getIdaObjects
	CREATE TABLE #getIdaObjects (clientId INT, apptypeId INT, instanceID INT, backupsetId INT, subclientID INT)
	CREATE INDEX getIdaObjects_instanceID_Idx ON #getIdaObjects(instanceID)
	IF OBJECT_ID('tempdb.dbo.#ClientInstanceDetails') IS NOT NULL
		DROP TABLE #ClientInstanceDetails
	CREATE TABLE #ClientInstanceDetails
	(
		[InstanceId] INT,
		[InstanceName] NVARCHAR(255),
		[ClientId] INT,
		[ClientName] NVARCHAR(255),
		[ClientDisplayName] NVARCHAR(255),
		[IdaType] INT,
		[IdaName] NVARCHAR(255),
		[DbId] INT,
		[DbName] NVARCHAR(255),
		[ClientType] INT DEFAULT 0,
		[LastBackupTime] INT DEFAULT 0,
		[Size] BIGINT DEFAULT 0,
		[SLA] VARCHAR(16) DEFAULT 'Met',
		[PlanId] INT DEFAULT '0',
		[PlanName] NVARCHAR(256) DEFAULT 'Not Assigned',
		[RecoveryModel] NVARCHAR(32) DEFAULT '',
		[IsCloudDB] NVARCHAR(16) DEFAULT 'false',
		[cloudVendorType] NVARCHAR(16) DEFAULT '0',
		[IsSnapEnabled] INT DEFAULT 0,
		[IsPartOfLatestFullCycle] INT DEFAULT 0
		PRIMARY KEY([InstanceId],[ClientId],[DbId],[DbName])
	)
	DECLARE @userId INT = ISNULL((SELECT @i_xmlString.value('(/Api_GetDatabasesRequest/@userId)[1]','INT')), 0)
	DECLARE @InstanceId INT = ISNULL((SELECT @i_xmlString.value('(/Api_GetDatabasesRequest/@instanceId)[1]','INT')), 0)
	DECLARE @appTypeId INT = ISNULL((SELECT @i_xmlString.value('(/Api_GetDatabasesRequest/@appTypeId)[1]','INT')), 0)
	DECLARE @propertyLevel INT = ISNULL((SELECT @i_xmlString.value('(/Api_GetDatabasesRequest/@propertyLevel)[1]','INT')), 10)
	DECLARE @cloudDB INT = ISNULL((SELECT @i_xmlString.value('(/Api_GetDatabasesRequest/@cloudDB)[1]','INT')), 0)
	--Get list of instances user has visibility to
	EXEC dbo.sec_getIdaObjectsForUser @userid, 7, 0, 1, '#getIdaObjects' -- 7 = SUBCLIENT_ENTITY from CvEntities.x
	IF @apptypeId <> 0
		DELETE	#getIdaObjects
		WHERE	apptypeId != @appTypeId
	IF (@cloudDB = 0)
	BEGIN
		INSERT	INTO #ClientInstanceDetails (InstanceId, InstanceName, ClientId, ClientName, ClientDisplayName, IdaType, IdaName, DbId, DbName)
		SELECT	DISTINCT I.id, I.name, SC.clientId, CL.name, CL.displayName, SC.appTypeId, Ida.displayName, 0, ISNULL(IP.attrVal, I.name)
		FROM    #getIdaObjects SEC
			INNER JOIN APP_InstanceName I
				ON SEC.instanceId = I.id
			INNER JOIN APP_Application SC
				ON I.id = SC.instance
			INNER JOIN APP_Client CL
				ON SC.clientId = CL.id
			INNER JOIN APP_iDAType IDA
				ON SC.appTypeId = IDA.[type]
			LEFT OUTER JOIN APP_InstanceProp IP
				ON I.id = IP.componentNameId AND IP.attrName = 'Oracle DB Name' AND IP.modified = 0
			WHERE     (I.status&4) = 0 AND (I.status&16) = 0 AND (I.status & 32) = 0 AND (I.status & 2048) = 0
					--#define CV_STATUS_DELETED           0x00004
					--#define CV_STATUS_DUMMY			  0x00010
					--#define CV_STATUS_HIDDEN            0x00020
					--#define CV_STATUS_ONDEMAND          0x00800
				  AND I.name NOT LIKE '+ASM%'
				  AND I.name NOT LIKE '-ASM%'
				  AND I.name <> '-MGMTDB'
				  AND I.name <> 'LISTENER'
AND IDA.[type] IN (22, 80, 61, 3)
		UNION ALL
		SELECT	DISTINCT I.id, I.name, SC.clientId, CL.name, CL.displayName, SC.appTypeId, Ida.displayName, B.id,
				CASE WHEN B.status & 8 <> 0 THEN I.name ELSE B.name END
		FROM	#getIdaObjects SEC
			INNER JOIN APP_InstanceName I
				ON SEC.instanceId = I.id
			INNER JOIN APP_Application SC
				ON I.id = SC.instance
			INNER JOIN APP_Client CL
				ON SC.clientId = CL.id
			INNER JOIN APP_iDAType IDA
				ON SC.appTypeId = IDA.[type]
			INNER JOIN APP_BackupSetName B
				ON SC.backupset = B.id
		WHERE	(I.status&4) = 0 AND (I.status&16) = 0 AND (I.status & 32) = 0 AND (I.status & 2048) = 0 AND (B.status & 32) = 0
AND IDA.[type] IN (135)
		UNION ALL
		SELECT	DISTINCT I.id, I.name, SC.clientId, CL.name, CL.displayName, SC.appTypeId, Ida.displayName, B.id, B.name
		FROM	#getIdaObjects SEC
			INNER JOIN APP_InstanceName I
				ON SEC.instanceId = I.id
			INNER JOIN APP_Application SC
				ON I.id = SC.instance
			INNER JOIN APP_Client CL
				ON SC.clientId = CL.id
			INNER JOIN APP_iDAType IDA
				ON SC.appTypeId = IDA.[type]
			INNER JOIN APP_BackupSetName B
				ON SC.backupset = B.id
		WHERE	(I.status&4) = 0 AND (I.status&16) = 0 AND (I.status & 32) = 0 AND (I.status & 2048) = 0 AND (B.status & 48) = 0
AND IDA.[type] IN (103,62,37)
		UNION ALL
		SELECT	DISTINCT I.id, I.name, SC.clientId, CL.name, CL.displayName, SC.appTypeId, Ida.displayName, DB.databaseId, DB.databaseName
		FROM	#getIdaObjects SEC
			INNER JOIN IdaDBExtendedProp DB
				ON SEC.instanceId = DB.instanceId
			INNER JOIN JMBkpStats bkp
				ON bkp.jobId = DB.latestBackupID AND bkp.agedTime = 0
			INNER JOIN APP_InstanceName I
				ON SEC.instanceId = I.id
			INNER JOIN APP_Application SC
				ON SEC.instanceId = SC.instance
			INNER JOIN APP_Client CL
				ON SC.clientId = CL.id
			INNER JOIN APP_IdaType IDA
				ON SC.appTypeId = IDA.[type]
		WHERE	(I.status&4) = 0 AND (I.status&16) = 0 AND (I.status & 32) = 0 AND (I.status & 2048) = 0
AND IDA.[type] IN (104, 125, 5)
		-- Following databases should be removed from count.
		DECLARE @sqlException TABLE (sqlNameId INT, name NVARCHAR(255))
		INSERT INTO @sqlException
			SELECT
				SN.id
				,SN2.name
			FROM sqlNames SN
				JOIN sqlNames2 SN2
					ON SN.sqlId = SN2.id
			WHERE SN2.name in (N'master', N'msdb', N'model')
		IF OBJECT_ID('tempdb.dbo.#ValidBackups') IS NOT NULL DROP TABLE #ValidBackups
		CREATE TABLE #ValidBackups (instanceId int, dbId INT, lastBkpId INT, lastBkpTime INT primary key (instanceId, dbId))
		INSERT INTO #ValidBackups
			SELECT
				SBD.instanceId,
				SBD.sqlNameId,
				SBD.id,
				SBD.backup_finish_Date
			FROM sqlDbBackupInfo SBD
				JOIN (SELECT
						SD.instanceId,
						SD.sqlNameId,
						MAX(SD.id) bkpID
					FROM sqlDbBackupInfo SD
						LEFT OUTER JOIN @sqlException SE
							ON SE.sqlNameId = SD.sqlNameId
					WHERE SE.name IS NULL
					GROUP BY SD.instanceId, SD.sqlNameId) TX
				ON TX.bkpID = SBD.id
			UNION
			SELECT
				SAB.instanceId,
				SAB.dbId,
				SAB.id,
				SAB.backup_finish_Date
			FROM sqlAnalysisDbBackupInfo SAB
				JOIN (SELECT
							SA.instanceId,
							SA.dbId,
							MAX(SA.id) bkpId
						FROM sqlAnalysisDbBackupInfo SA
						GROUP BY SA.instanceId, SA.dbId) TX
					ON TX.bkpId = SAB.id
		INSERT	INTO #ClientInstanceDetails (InstanceId, InstanceName, ClientId, ClientName, ClientDisplayName, IdaType, IdaName, DbId, DbName, RecoveryModel, LastBackupTime)
			SELECT	DISTINCT I.id, I.name, SC.clientId, CL.name, CL.displayName, SC.appTypeId, Ida.displayName, DB.databaseId, DB.databaseName,
					CASE DB.RecoveryModel WHEN 1 THEN N'FULL' WHEN 2 THEN N'BULK LOGGED' WHEN 3 THEN N'SIMPLE' END,
					CASE WHEN DB.LastOkBackupTime IS NOT NULL
							AND DB.LastOkBackupTime > TH.lastBkpTime
					THEN DB.LastOkBackupTime ELSE TH.lastBkpTime END BkpTime
			FROM	#getIdaObjects SEC
				INNER JOIN SqlDatabaseInfo DB
					ON SEC.instanceId = DB.instanceId
				INNER JOIN APP_InstanceName I
					ON SEC.instanceId = I.id
				INNER JOIN APP_Application SC
					ON SEC.instanceId = SC.instance AND SC.id = DB.appId
				INNER JOIN APP_Client CL
					ON SC.clientId = CL.id
				INNER JOIN APP_IdaType IDA
					ON SC.appTypeId = IDA.[type]
				INNER JOIN #ValidBackups TH ON TH.instanceId = DB.instanceId
					AND TH.dbId = DB.databaseId AND DB.LastBackupSetId = TH.lastBkpId
				LEFT JOIN @sqlException SE ON SE.name = DB.databaseName
WHERE	(I.status & 0x00004) = 0
AND (I.status & 0x00010) = 0
AND (I.status & 0x00020) = 0
AND IDA.[type] = 81
				AND SE.name IS NULL -- filter system databases
		DELETE VB
		FROM #ValidBackups VB
			JOIN #ClientInstanceDetails	CI
				ON CI.InstanceId = VB.instanceId
					AND CI.DbId = VB.dbId
		INSERT	INTO #ClientInstanceDetails (InstanceId, InstanceName, ClientId, ClientName, ClientDisplayName, IdaType, IdaName, DbId, DbName, RecoveryModel, LastBackupTime)
			SELECT	DISTINCT I.id, I.name, SC.clientId,
					CL.name, CL.displayName, SC.appTypeId, Ida.displayName,
					DB.sqlNameId, SN2.name,
					CASE DB.type
						WHEN 'L' THEN N'FULL'
						ELSE N'SIMPLE' END,
					DB.backup_finish_Date
			FROM #getIdaObjects SEC
				JOIN sqlDbBackupInfo DB
					ON SEC.instanceId = DB.instanceId
				JOIN #ValidBackups VB
					ON VB.instanceId = DB.instanceId
						AND VB.dbId = DB.sqlNameId
						AND VB.lastBkpId = DB.id
				JOIN sqlNames SN
					ON SN.id = DB.sqlNameId
						AND SN.type = 1
				JOIN sqlNames2 SN2
					ON SN2.id = SN.sqlId
						AND SN2.type = 1
				JOIN APP_InstanceName I
					ON SEC.instanceId = I.id
				INNER JOIN APP_Application SC
					ON SEC.instanceId = SC.instance AND SC.id = DB.appId
				INNER JOIN APP_Client CL
					ON SC.clientId = CL.id
				INNER JOIN APP_IdaType IDA
					ON SC.appTypeId = IDA.[type]
				LEFT JOIN @sqlException SE ON SE.name = SN2.name
WHERE (I.status & 0x00004) = 0
AND (I.status & 0x00010) = 0
AND (I.status & 0x00020) = 0
AND IDA.[type] = 81
				AND SE.name IS NULL -- filter system databases
		UNION
			SELECT	DISTINCT I.id, I.name, SC.clientId, CL.name,
					CL.displayName, SC.appTypeId, Ida.displayName,
					DB.dbId, SN2.name, N'NA' /*No RM*/,
					DB.backup_finish_Date
			FROM #getIdaObjects SEC
				JOIN sqlAnalysisDbBackupInfo DB
					ON SEC.instanceId = DB.instanceId
				JOIN #ValidBackups VB
					ON VB.instanceId = DB.instanceId
						AND VB.dbId = DB.dbId
						AND VB.lastBkpId = DB.id
				JOIN sqlNames SN
					ON SN.id = DB.dbId
						AND SN.type = 1
				JOIN sqlNames2 SN2
					ON SN2.id = SN.sqlId
						AND SN2.type = 1
				JOIN APP_InstanceName I
					ON SEC.instanceId = I.id
				INNER JOIN APP_Application SC
					ON SEC.instanceId = SC.instance AND SC.id = DB.appId
				INNER JOIN APP_Client CL
					ON SC.clientId = CL.id
				INNER JOIN APP_IdaType IDA
					ON SC.appTypeId = IDA.[type]
WHERE (I.status & 0x00004) = 0
AND (I.status & 0x00010) = 0
AND (I.status & 0x00020) = 0
AND IDA.[type] = 81
	END
	BEGIN
		INSERT	INTO #ClientInstanceDetails (InstanceId, InstanceName, ClientId, ClientName, ClientDisplayName, IdaType, IdaName, DbId, DbName, Size)
		SELECT	DISTINCT I.id, I.name, SC.clientId, CL.name, CL.displayName, SC.appTypeId,
(CASE	WHEN IP.attrVal = '22' THEN 'DynamoDB'
WHEN IP.attrVal = '23' THEN 'Cosmos DB (SQL API)'
WHEN IP.attrVal = '28' THEN 'Neptune'
WHEN IP.attrVal = '29' THEN 'ElastiCache'
WHEN IP.attrVal = '32' THEN 'Table storage'
WHEN IP.attrVal = '37' THEN 'Spanner'
				ELSE	IDA.displayName END),
				DB.databaseId, DB.databaseName, DB.databaseSize
		FROM	#getIdaObjects SEC
			INNER JOIN IdaDBExtendedProp DB
				ON SEC.instanceId = DB.instanceId
			INNER JOIN JMBkpStats bkp
				ON bkp.jobId = DB.latestBackupID AND bkp.agedTime = 0
			INNER JOIN APP_InstanceName I
				ON SEC.instanceId = I.id
			INNER JOIN APP_InstanceProp IP
				ON I.id = IP.componentNameId AND IP.attrName = 'Cloud Apps Instance Type' AND IP.modified = 0 AND IP.attrVal IN ('22', '23', '28', '29', '32', '37')
			INNER JOIN APP_Application SC
				ON SEC.instanceId = SC.instance
			INNER JOIN APP_Client CL
				ON SC.clientId = CL.id
			INNER JOIN APP_IdaType IDA
ON SC.appTypeId = IDA.[type] AND IDA.[type] = 134
		WHERE	(I.status&4) = 0 AND (I.status&16) = 0 AND (I.status & 32) = 0 AND (I.status & 2048) = 0
		-- RDS like instances
		INSERT	INTO #ClientInstanceDetails (InstanceId, InstanceName, ClientId, ClientName, ClientDisplayName, IdaType, IdaName, DbId, DbName, Size)
		SELECT	DISTINCT I.id, I.name, SC.clientId, CL.name, CL.displayName, SC.appTypeId,
(CASE	WHEN IP.attrVal = '4' THEN 'RDS'
WHEN IP.attrVal = '26' THEN 'Redshift'
WHEN IP.attrVal = '27' THEN 'DocumentDB'
				ELSE	IDA.displayName END),
				0, DBMap.dbName + ' [' + CVR.RegionCode + ']',
                ISNULL(DBMap.rdsXml.value('(/App_RDSAttributes/@size)[1]', 'bigint'), 0)
		FROM	#getIdaObjects SEC
			INNER JOIN (SELECT subclientId, dbName, CAST(rsdAttributes AS XML) as rdsXml, regionId
						FROM APP_RDSSubclientContentMap
						WHERE modified = 0) DBMap
				ON SEC.subclientID = DBMap.subclientId
			INNER JOIN APP_InstanceName I
				ON SEC.instanceId = I.id
			INNER JOIN APP_InstanceProp IP
				ON I.id = IP.componentNameId AND IP.attrName = 'Cloud Apps Instance Type' AND IP.modified = 0 AND IP.attrVal IN ('4', '26', '27')
			INNER JOIN APP_Application SC
				ON SEC.subclientID = SC.id
			INNER JOIN APP_Client CL
				ON SC.clientId = CL.id
			INNER JOIN APP_IdaType IDA
ON SC.appTypeId = IDA.[type] AND IDA.[type] = 134
			INNER JOIN App_CloudVendorRegion CVR
                ON CVR.Id = DBMap.regionId
		WHERE	(I.status&4) = 0 AND (I.status&16) = 0 AND (I.status & 32) = 0 AND (I.status & 2048) = 0
	END
	--This additional name check is required as the defaultInstance of DB2 is not marked hidden or dummy
DELETE #ClientInstanceDetails WHERE InstanceName = 'defaultInstanceName' AND IdaType = 103 -- Exclude default instances of DB2 Multinode
  IF OBJECT_ID('tempdb..#SqlDbPlanDetails') IS NOT NULL DROP TABLE #SqlDbPlanDetails
		CREATE TABLE #SqlDbPlanDetails (dbName NVARCHAR(256), subclientId int, attrVal NVARCHAR(256),
			instance int, planId int, planName nvarchar(1024),
			primary key (instance, dbName))
	--Update plan info
IF @appTypeId = 81 OR @appTypeId = 0
    BEGIN
		INSERT INTO #SqlDbPlanDetails
			SELECT ASPX.attrName as DBName,
				ASPX.componentNameId as subclientId,
				CASE
					WHEN ASP.attrVal IS NOT NULL THEN ASP.attrVal
					WHEN ABP.attrVal IS NOT NULL THEN ABP.attrVal
					WHEN ACP.attrVal IS NOT NULL THEN ACP.attrVal
				END AS attrVal,
				AC.instance,
				0,
				N''
			FROM APP_SubClientProp ASPX
			-- Consider the plan details for the databases that are being actively backed-up.
			-- Do not consider plan for databases thate removed from subclient content or from non-active/deleted subclient.
			JOIN APP_Application AC
				ON AC.id = ASPX.componentNameId
					AND ASPX.attrType = 118
					AND ASPX.modified = 0
					AND (AC.subclientStatus & (2|4)) = 0
			-- Distinct subclient which is of type 1.
			JOIN (SELECT
						componentNameId
					FROM APP_SubclientProp ASX
					WHERE
						ASX.attrName = N'mssql subclient type' AND
						ASX.cs_attrName = CHECKSUM(N'mssql subclient type') AND
						ASX.modified = 0 AND
						ASX.attrVal = N'1'
					GROUP BY componentNameId) OMS
				ON OMS.componentNameId = AC.id
			LEFT JOIN App_ClientProp ACP
				ON ACP.componentNameId = AC.clientId
AND ACP.attrName = 'Associated Plan'
					AND ACP.modified = 0
			LEFT JOIN APP_BackupSetProp ABP
				ON ABP.componentNameId = AC.backupSet
AND ABP.attrName = 'Associated Plan'
					AND ABP.modified = 0
			LEFT JOIN APP_SubClientProp ASP
				ON ASP.componentNameId = AC.id
AND ASP.attrName = 'Associated Plan'
AND ASP.cs_attrName = CHECKSUM(N'Associated Plan')
					AND ASP.modified = 0
			WHERE ACP.attrVal IS NOT NULL
				OR ABP.attrVal IS NOT NULL
				OR ASP.attrVal IS NOT NULL
    UPDATE PD SET
			PD.planId = PL.id,
			PD.planName = PL.name
		FROM #SqlDbPlanDetails PD
			JOIN App_Plan PL
				ON PD.attrVal = CAST(PL.id AS NVARCHAR(12))
    END
	IF EXISTS(SELECT 1 FROM #SqlDbPlanDetails)
	BEGIN
		UPDATE I SET PlanId = ISNULL(IP.planId, 0),
                        PlanName = ISNULL(IP.planName, '')
                FROM    #ClientInstanceDetails I
                    INNER JOIN #SqlDbPlanDetails IP
                        ON IP.instance = I.InstanceId
                            AND IP.dbName = I.DbName
WHERE I.IdaType = 81
		-- Update other agents.
		UPDATE	#ClientInstanceDetails
				SET		PlanId = ISNULL(AP.id, 0),
						PlanName = ISNULL(AP.name, '')
				FROM	#ClientInstanceDetails I
					INNER JOIN APP_InstanceProp IP
ON IP.componentNameId = I.InstanceId  AND IP.attrName = 'Associated Plan' AND IP.modified=0
					LEFT OUTER JOIN App_Plan AP WITH(NOLOCK)
ON CAST(AP.id AS NVARCHAR(32)) = IP.attrVal AND ((AP.flag & 0x00004) = 0) AND ((AP.flag & 0x40000000) = 0)
WHERE I.IdaType <> 81
	END
  ELSE
  BEGIN
        --Update plan info
      	UPDATE	#ClientInstanceDetails
				SET		PlanId = ISNULL(AP.id, 0),
						PlanName = ISNULL(AP.name, '')
				FROM	#ClientInstanceDetails I
					INNER JOIN APP_InstanceProp IP
ON IP.componentNameId = I.InstanceId  AND IP.attrName = 'Associated Plan' AND IP.modified=0
					LEFT OUTER JOIN App_Plan AP WITH(NOLOCK)
ON CAST(AP.id AS NVARCHAR(32)) = IP.attrVal AND ((AP.flag & 0x00004) = 0) AND ((AP.flag & 0x40000000) = 0)
  END
	--Update SLA status
	IF object_id('tempdb..#SLA_EntityInfo') IS NOT NULL DROP TABLE #SLA_EntityInfo
	CREATE TABLE #SLA_EntityInfo (
	clientId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT, -- entity IDs to set
	status INT, category INT, slaDays INT)
	insert into #SLA_EntityInfo(clientId, appTypeId, instanceId, backupsetId, appId)
	SELECT  C.clientId, C.IdaType, C.InstanceId, A.backupset, A.id
	FROM #ClientInstanceDetails C
	INNER JOIN APP_Application A
		ON C.instanceId = A.instance
AND (A.subclientStatus & 0x00004) = 0
AND (A.subclientStatus & 0x00010) = 0
AND (A.subclientStatus & 0x00020) = 0
	exec RptGetSLAOfEntities 5
	UPDATE	I
	SET		I.SLA = (CASE WHEN R.SlaStatus = 1 THEN 'Met' WHEN (R.SlaStatus = 2 OR R.SlaStatus IS NULL) THEN 'Missed' ELSE 'Excluded' END)
	FROM	#ClientInstanceDetails I
		LEFT OUTER JOIN (
				SELECT	SEC.instanceId, MIN((CASE WHEN SEC.status = 1 THEN 1 WHEN SEC.status = 2 THEN 2 ELSE 3 END)) AS SlaStatus
				FROM	#SLA_EntityInfo SEC
				GROUP BY SEC.instanceId) R
		ON	I.InstanceId = R.instanceId
WHERE	I.IdaType IN (22, 80, 61, 3)
	--Update SLA status
	UPDATE	I
	SET		I.SLA = (CASE WHEN R.SlaStatus = 1 THEN 'Met' WHEN (R.SlaStatus = 2 OR R.SlaStatus IS NULL) THEN 'Missed' ELSE 'Excluded' END)
	FROM	#ClientInstanceDetails I
		LEFT OUTER JOIN (
				SELECT	SEC.backupsetId, MIN((CASE WHEN SEC.status = 1 THEN 1 WHEN SEC.status = 2 THEN 2 ELSE 3 END)) AS SlaStatus
				FROM	#SLA_EntityInfo SEC
				GROUP BY SEC.backupsetId) R
		ON	I.DbId = R.backupsetId
WHERE	I.IdaType IN (135, 103,62, 37)
	--Update SLA status
	UPDATE	I
	SET		I.SLA = (CASE WHEN R.SlaStatus = 1 THEN 'Met' WHEN (R.SlaStatus = 2 OR R.SlaStatus IS NULL) THEN 'Missed' ELSE 'Excluded' END)
	FROM	#ClientInstanceDetails I
		LEFT OUTER JOIN (
				SELECT	SEC.instanceId, DB.databaseName, MIN((CASE WHEN SEC.status = 1 THEN 1 WHEN SEC.status = 2 THEN 2 ELSE 3 END)) AS SlaStatus
				FROM	#SLA_EntityInfo SEC
					INNER JOIN IdaDbExtendedProp DB ON SEC.appId = DB.subclientId
				GROUP BY SEC.instanceId, DB.databaseName) R
		ON	I.DbName = R.databaseName AND I.instanceId = R.instanceId
WHERE	I.IdaType IN (104, 125, 5)
	--Update SLA status for MSSQL
	UPDATE	I
	SET		I.SLA = (CASE WHEN R.SlaStatus = 1 THEN 'Met' WHEN (R.SlaStatus = 2 OR R.SlaStatus IS NULL) THEN 'Missed' ELSE 'Excluded' END)
	FROM	#ClientInstanceDetails I
		LEFT OUTER JOIN (
				SELECT	SEC.instanceId, DB.databaseId, MIN((CASE WHEN SEC.status = 1 THEN 1 WHEN SEC.status = 2 THEN 2 ELSE 3 END)) AS SlaStatus
				FROM	#SLA_EntityInfo SEC
					INNER JOIN SqlDatabaseInfo DB ON SEC.appId = DB.appId
				GROUP BY SEC.instanceId, DB.databaseId) R
		ON	I.DbId = R.databaseId AND I.instanceId = R.instanceId
WHERE	I.IdaType = 81
	--Update SLA status for RDS, Redshift and Documentum DB
	UPDATE	I
	SET		I.SLA = (CASE WHEN R.SlaStatus = 1 THEN 'Met' WHEN (R.SlaStatus = 2 OR R.SlaStatus IS NULL) THEN 'Missed' ELSE 'Excluded' END)
	FROM	#ClientInstanceDetails I
		LEFT OUTER JOIN (
				SELECT	SEC.instanceId, RDSMap.dbName, MIN((CASE WHEN SEC.status = 1 THEN 1 WHEN SEC.status = 2 THEN 2 ELSE 3 END)) AS SlaStatus
				FROM	#SLA_EntityInfo SEC
					INNER JOIN APP_RDSSubclientContentMap RDSMap
						ON SEC.appId = RDSMap.subclientId
				GROUP BY SEC.instanceId, RDSMap.dbName) R
		ON	I.DbName = R.dbName AND I.instanceId = R.instanceId
WHERE	I.IdaType = 134 AND I.IdaName IN ('RDS', 'Redshift', 'DocumentDB')
	--Update SLA status for DynamoDb, Table storage, CosmosDb, Neptune and ElastiCache
	UPDATE	I
	SET		I.SLA = (CASE WHEN R.SlaStatus = 1 THEN 'Met' WHEN (R.SlaStatus = 2 OR R.SlaStatus IS NULL) THEN 'Missed' ELSE 'Excluded' END)
	FROM	#ClientInstanceDetails I
		LEFT OUTER JOIN (
				SELECT	SEC.instanceId, IDB.databaseName, MIN((CASE WHEN SEC.status = 1 THEN 1 WHEN SEC.status = 2 THEN 2 ELSE 3 END)) AS SlaStatus
				FROM	#SLA_EntityInfo SEC
					INNER JOIN IdaDBExtendedProp IDB
						ON SEC.appId = IDB.subclientID
				GROUP BY SEC.instanceId, IDB.databaseName) R
		ON	I.DbName = R.databaseName AND I.instanceId = R.instanceId
WHERE	I.IdaType = 134 AND I.IdaName IN ('DynamoDB', 'Cosmos DB (SQL API)', 'Neptune', 'ElastiCache', 'Table storage', 'Spanner')
	--Update type for SAP HANA instances
	UPDATE	I
	SET		clientType = IP.attrVal
	FROM	#ClientInstanceDetails I
		INNER JOIN APP_InstanceProp IP
		ON	IP.componentNameId = I.InstanceId
			AND IP.attrName = 'DB Container Mode'
AND I.IdaType = 135
			AND IP.modified = 0
	-- Update type for SQL instances
    UPDATE I
	SET  clientType = (CASE   WHEN IP.attrVal = 'DataBase Engine' THEN 1
		WHEN IP.attrVal = 'Azure DataBase Engine' THEN 2
		WHEN IP.attrVal = 'Analysis Services' THEN 3
		WHEN IP.attrVal = 'Aws DataBase Engine' THEN 4
		WHEN IP.attrVal = 'Analysis Services' THEN 5
		END)
	FROM   #ClientInstanceDetails I
	INNER JOIN APP_InstanceProp IP
	ON     IP.componentNameId = I.InstanceId
	AND IP.attrName = 'MSSQL Server Type'
	AND I.IdaType = 81
	AND IP.modified = 0
	IF OBJECT_ID('tempdb.dbo.#lastSuccBackupJob') IS NOT NULL DROP TABLE dbo.#lastSuccBackupJob
	CREATE TABLE #lastSuccBackupJob (instanceId INT, backupsetId INT, backupJobId INT, commCellId INT, servStartDate INT, size BIGINT)
	INSERT INTO #lastSuccBackupJob
	SELECT InstanceId, backupset, jobId, commCellID, servStartDate, totalUnCompBytes
	FROM
	(
		SELECT	I.InstanceId, A.backupset, B.jobId, B.commCellID, B.servStartDate, B.totalUnCompBytes, ROW_NUMBER() OVER (PARTITION BY I.instanceId, A.backupset ORDER BY B.servStartDate DESC) AS rowId
		FROM	#ClientInstanceDetails I
			INNER JOIN APP_IdaType IDA
				ON I.IdaType = IDA.type
			INNER JOIN APP_Application A
				ON I.instanceId = A.instance
			INNER JOIN JMBkpStats B
				ON A.id = B.appId AND (B.status IN (1, 14) OR (B.status = 3 AND IDA.isCWEJobValid = 1))
	) S
	WHERE S.rowId = 1
	-- update last backup time for all agents having entry in IdaDBExtendedProp
	UPDATE I
	SET I.LastBackupTime = ida.lastBackupTime
	FROM #ClientInstanceDetails I
	INNER JOIN IdaDBExtendedProp ida ON I.DbName = ida.databaseName AND I.InstanceId = ida.instanceID
	WHERE I.LastBackupTime = 0
	--Oracle and Oracle RAC
	UPDATE	I
	SET		I.Size = IP1.attrVal,
			I.LastBackupTime = IP2.attrVal
	FROM	#ClientInstanceDetails I
		LEFT OUTER JOIN APP_InstanceProp IP1
			ON I.instanceId = IP1.componentNameId AND IP1.attrName = 'Last Data Backup Size'
		LEFT OUTER JOIN APP_InstanceProp IP2
			ON I.instanceId = IP2.componentNameId AND IP2.attrName = 'Last Backup Completion Time'
	WHERE	ISNULL(IP1.attrVal, '0') <> '0'
		AND	ISNULL(IP2.attrVal, '0') <> '0'
AND I.IdaType IN (22, 80, 61, 3)
	--If we don't have size info at instance level, populate from oracleDataFiles table
	UPDATE	I
	SET		I.Size = OU.DBSIZE
	FROM	#ClientInstanceDetails I
		INNER JOIN
			(SELECT O.instId, SUM(O.maxDBDataSize) as DBSIZE FROM oracleDataFiles O WHERE O.deleteFlag = 0 GROUP BY O.instId) OU
		ON I.instanceId = OU.instId AND OU.DBSIZE IS NOT NULL
WHERE	I.IdaType IN (22, 80)
		AND	I.Size = 0
	--DB2
	UPDATE	I
	SET		I.Size = OU.DBSIZE
	FROM	#ClientInstanceDetails I
		INNER JOIN
			(SELECT O.instId, O.backupsetId, SUM(O.maxDBDataSize) as DBSIZE FROM oracleDataFiles O WHERE O.deleteFlag = 0 GROUP BY O.instId, O.backupsetId) OU
		ON I.instanceId = OU.instId AND I.DbId = OU.backupsetId AND OU.DBSIZE IS NOT NULL
WHERE	I.IdaType IN (103,62,37)
		AND	I.Size = 0
	--SAP HANA
	UPDATE	I
	SET		I.Size = CAST(B.attrVal AS BIGINT)
	FROM	#ClientInstanceDetails I
		INNER JOIN APP_BackupSetProp B
			ON I.DbId = B.componentNameId AND B.attrName = 'Last Data Backup Size' AND B.modified = 0
AND I.IdaType = 135
	WHERE	I.Size = 0
	--Postgres and MySQL
	UPDATE	I
	SET		I.size = D.databaseSize
	FROM	#ClientInstanceDetails I
		INNER JOIN	IdaDBExtendedProp D
			ON I.instanceId = D.instanceID
			AND I.DbName = D.databaseName
WHERE	I.IdaType IN (104, 125, 5)
		AND	I.Size = 0
	--SAP HANA
	UPDATE	I
	SET		I.LastBackupTime = (CASE WHEN I.LastBackupTime > 0 THEN I.LastBackupTime ELSE J.servStartDate END),
			I.Size = (CASE WHEN I.Size > 0 THEN I.Size ELSE J.size END)
	FROM	#ClientInstanceDetails I
		INNER JOIN #lastSuccBackupJob J
ON I.DbId = J.backupsetId AND I.IdaType = 135
	WHERE	(I.LastBackupTime = 0 OR I.Size = 0)
	--SQL Server, size of data
	IF OBJECT_ID('tempdb.dbo.#SqlFullData') IS NOT NULL DROP TABLE dbo.#SqlFullData
	CREATE TABLE #SqlFullData(sqlNameId INT, backup_size bigint, instanceid INT, rn INT)
	INSERT	INTO #SqlFullData
		SELECT
			I.sqlNameId,
			SUM(SF.pageSize),
			I.instanceId,
			I.rn
		FROM
			(SELECT
					SDB.id,
					SDB.sqlNameId,
					SDB.instanceId,
				ROW_NUMBER() OVER (PARTITION BY  SDB.sqlNameId, SDB.instanceId ORDER BY SDB.backup_finish_date DESC) rn
			FROM	sqlDbBackupInfo SDB
			WHERE	SDB.type = 'D') I
			JOIN sqlFileBackupInfo SF
				ON SF.sqlDbBackupInfoId = I.id
		WHERE	rn = 1
		GROUP BY I.sqlNameId, I.instanceId, I.rn
		-- SQL Analysis Services
		UNION ALL
		SELECT	I.dbId, I.backup_size, I.instanceId, I.rn FROM
			(SELECT	SADB.dbId, SADB.backup_size, SADB.instanceId,
				ROW_NUMBER() OVER (PARTITION BY  SADB.dbId, SADB.instanceId ORDER BY SADB.backup_finish_date DESC) rn
			FROM	sqlAnalysisDbBackupInfo SADB
			WHERE	SADB.type = 'D') I
		WHERE	rn = 1
	UPDATE	I
	SET		I.Size = SqlData.backup_size
	FROM	#ClientInstanceDetails I
		INNER JOIN #SqlFullData SqlData
			ON SqlData.instanceId = I.instanceId
				AND I.DbId = SqlData.sqlNameId
WHERE	I.IdaType = 81
		AND	I.Size = 0
		AND SqlData.backup_size IS NOT NULL
	IF OBJECT_ID('tempdb.dbo.#SqlFullData') IS NOT NULL DROP TABLE dbo.#SqlFullData
	--All Agents
	UPDATE	I
	SET		I.LastBackupTime = (CASE WHEN I.LastBackupTime > 0 THEN I.LastBackupTime ELSE J.servStartDate END),
			I.Size = (CASE WHEN I.Size > 0 THEN I.Size ELSE J.size END)
	FROM	#ClientInstanceDetails I
		INNER JOIN #lastSuccBackupJob J
ON I.InstanceId = J.instanceId AND I.IdaType <> 135
	WHERE	(I.LastBackupTime = 0 OR I.Size = 0)
	IF OBJECT_ID('tempdb.dbo.#lastSuccBackupJob') IS NOT NULL DROP TABLE dbo.#lastSuccBackupJob
	--Set CloudDB info
	UPDATE I
    SET I.IsCloudDB = 'true' , I.cloudVendorType = ISNULL(acp2.attrVal, I.cloudVendorType),
		I.IdaName = ( CASE WHEN I.IdaType IN (125, 104) AND I.InstanceName like 'aurora%' THEN 'Aurora ' + I.IdaName
WHEN I.IdaType IN (104, 125) AND I.InstanceName like 'mariadb%' THEN 'MariaDB'
					  ELSE I.IdaName END)
    FROM #ClientInstanceDetails I
        INNER JOIN APP_ClientProp acp
            on I.ClientId = acp.componentNameId AND acp.attrName = 'Client App Type' AND acp.attrVal = '1' AND acp.modified = 0
		LEFT OUTER JOIN APP_ClientProp acp2
			on I.ClientId = acp2.componentNameId AND acp2.attrName = 'Cloud Hypervisor Type' AND acp2.modified = 0
	-- update isSnapEnabled flag
	-- for SAP HANA, DB2 check the subclients under the backupset
	Update I
    SET I.IsSnapEnabled = 1
    FROM #ClientInstanceDetails I
    INNER JOIN (
        SELECT DISTINCT app.backupSet FROM APP_Application app WITH (NOLOCK)
		INNER JOIN #ClientInstanceDetails CI ON CI.DbId = app.backupSet
        INNER JOIN APP_SubClientProp sc WITH (NOLOCK) ON app.id = sc.componentNameId
        WHERE sc.attrName = 'Enable Snap Backups' AND sc.cs_attrName = CHECKSUM(N'Enable Snap Backups')
			AND sc.modified = 0 AND sc.attrVal = '1'
AND app.appTypeId IN (135, 103, 62, 37)
    ) T ON T.backupSet = I.DbId
	-- for Oracle, Informix check the subclients under instance
	Update I
    SET I.IsSnapEnabled = 1
    FROM #ClientInstanceDetails I
    INNER JOIN (
        SELECT DISTINCT app.instance FROM APP_Application app WITH (NOLOCK)
		INNER JOIN #ClientInstanceDetails CI ON CI.InstanceId = app.instance
        INNER JOIN APP_SubClientProp sc WITH (NOLOCK) ON app.id = sc.componentNameId
        WHERE sc.attrName = 'Enable Snap Backups' AND sc.modified = 0
			AND sc.attrVal = '1' AND sc.cs_attrName = CHECKSUM(N'Enable Snap Backups')
AND app.appTypeId IN (22, 80, 61, 3)
    ) T ON T.instance = I.InstanceId
	-- for MySQL, PostGreSQL, Sybase, Cloud Apps check the subclient associated with the database name
    Update I
    SET I.IsSnapEnabled = 1
    FROM #ClientInstanceDetails I
    INNER JOIN (
        SELECT DISTINCT CI.DbName, CI.InstanceId FROM #ClientInstanceDetails CI
		INNER JOIN IdaDBExtendedProp DB WITH (NOLOCK) ON CI.DbName = DB.databaseName
		INNER JOIN APP_Application app WITH (NOLOCK) ON DB.subclientID = app.id
        INNER JOIN APP_SubClientProp sc WITH (NOLOCK) ON DB.subclientID = sc.componentNameId
        WHERE sc.attrName = 'Enable Snap Backups' AND sc.modified = 0 AND sc.attrVal = '1'
			AND sc.cs_attrName = CHECKSUM(N'Enable Snap Backups')
AND app.appTypeId IN (104, 125, 5, 134)
    ) T ON T.DbName = I.DbName AND T.InstanceId = I.InstanceId
	-- for MSSQL check the subclient associated with the database name
	-- IsSnapEnabled flag is being used by GUI to decide if some backup options should be visible.
	-- DIFF backup option to be hidden for block level subclient.
	-- So using the same field to denote the block level subclient.
	-- Without chnages, this flag should not be used for restore options.
	Update I
    SET I.IsSnapEnabled = 1
    FROM #ClientInstanceDetails I
    INNER JOIN (
        SELECT DISTINCT CI.DbId FROM #ClientInstanceDetails CI
		INNER JOIN SqlDatabaseInfo DB WITH (NOLOCK) ON CI.DbId = DB.databaseId
		INNER JOIN APP_Application app WITH (NOLOCK) ON DB.appId = app.id
        INNER JOIN APP_SubClientProp sc WITH (NOLOCK) ON DB.appId = sc.componentNameId
        WHERE sc.attrName = 'Enable Snap Backups' AND sc.modified = 0 AND sc.attrVal = '1'
			AND sc.cs_attrName = CHECKSUM(N'Enable Snap Backups')
AND app.appTypeId = 81
    ) T ON T.DbId = I.DbId
	Update I
    SET I.IsSnapEnabled = 1
    FROM #ClientInstanceDetails I
    INNER JOIN (
        SELECT DISTINCT CI.DbId
			FROM #ClientInstanceDetails CI
				INNER JOIN SqlDatabaseInfo DB WITH (NOLOCK)
					ON CI.DbId = DB.databaseId
				INNER JOIN APP_Application app WITH (NOLOCK)
					ON DB.appId = app.id
				INNER JOIN APP_SubClientProp sc WITH (NOLOCK)
					ON DB.appId = sc.componentNameId
			WHERE sc.attrName = 'Use block-level backup with optimize recovery'
				AND sc.modified = 0 AND sc.attrVal = '1'
				AND sc.cs_attrName = CHECKSUM(N'Use block-level backup with optimize recovery')
AND app.appTypeId = 81
    ) T ON T.DbId = I.DbId
	Update I
	SET I.IsCloudDB = 'true'
	FROM #ClientInstanceDetails I
		INNER JOIN APP_ClientProp acp
			on I.ClientId = acp.componentNameId
				AND acp.attrName = 'Cloud Hypervisor Type'
				AND acp.attrVal <> '0'
				AND acp.modified = 0
	-- For SQL, set cloud DB flag.
	UPDATE I
		SET I.IsCloudDB = 'true'
	FROM #ClientInstanceDetails I
		INNER JOIN APP_InstanceProp AI
			ON I.InstanceId = AI.componentNameId
				AND AI.attrName = 'MSSQL Server Type'
AND I.IdaType = 81
				AND AI.modified = 0
	WHERE AI.attrVal IN ('Azure DataBase Engine',
							'Aws DataBase Engine',
							'Azure Managed DataBase Engine')
	-- set isPartOfLatestFullCycle flag for all the databases having entry in IdaDBExtendedProp
	UPDATE I
	SET I.IsPartOfLatestFullCycle = 1
	FROM #ClientInstanceDetails I
	INNER JOIN IdaDBExtendedProp DB WITH (NOLOCk) ON I.DbName = DB.databaseName AND I.InstanceId = DB.instanceID
	INNER JOIN (
		SELECT MAX(JM.jobId) AS latestFullJob, DB.subclientID FROM JMBkpStats JM WITH (NOLOCk)
		INNER JOIN IdaDBExtendedProp DB WITH (NOLOCk) ON DB.subclientID = JM.appId
		INNER JOIN #ClientInstanceDetails I ON I.InstanceId = DB.instanceID
		INNER JOIN APP_IdaType IDA WITH (NOLOCk) ON I.IdaType = IDA.type
		WHERE bkpLevel = 1 AND (JM.status IN (1, 14) OR (JM.status = 3 AND IDA.isCWEJobValid = 1))
		GROUP BY DB.subclientID
	) T ON T.subclientID = DB.subclientID
	WHERE T.latestFullJob <= DB.latestBackupID
	SET @o_xmlString =
	(
		SELECT
		(	SELECT	0 AS '@errorCode',
					N'' AS '@errorMessage'
			FOR XML PATH('error'), TYPE),
		(	SELECT	D.LastBackupTime AS '@backupTime',
					D.IsCloudDB AS '@isCloudDB',
					D.cloudVendorType as '@cloudVendorType',
					D.Size AS '@backupSize',
					D.SLA AS '@sla',
					D.IsSnapEnabled AS '@isSnapEnabled',
					D.clientType AS '@clientType',
					D.RecoveryModel AS '@sqlRecoveryModel',
					D.IsPartOfLatestFullCycle AS '@isPartOfLatestFullCycle',
					D.InstanceId AS 'instance/@instanceId',
					D.InstanceName AS 'instance/@instanceName',
					D.IdaType AS 'instance/@applicationId',
					D.IdaName AS 'instance/@appName',
					D.ClientId AS 'instance/@clientId',
					D.ClientName AS 'instance/@clientName',
					D.ClientDisplayName AS 'instance/@displayName',
					D.DbId AS 'backupset/@backupsetId',
					D.DbName AS 'backupset/@backupsetName',
					D.PlanId AS 'plan/@planId',
					D.PlanName AS 'plan/@planName'
			FROM #ClientInstanceDetails AS D for XML PATH('dbInstance'), TYPE)
		FOR XML PATH(''), ROOT('Api_GetDatabasesResponse')
	)
END TRY
BEGIN CATCH
PRINT  'INSIDE CATCH BLOCK WITH FOLLOWING ERROR:
	ERROR CODE: ' + CAST(ERROR_NUMBER() AS VARCHAR) + '
	PROC NAME: ' + ISNULL(ERROR_PROCEDURE(), '???') + '
	ERROR LINE NO: ' + CAST(ERROR_LINE() AS VARCHAR)  + '
	ERROR MESSAGE: ' + ERROR_MESSAGE() + '
	ERROR SEVERITY: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +  '
	ERROR STATE: ' + CAST(ERROR_STATE() AS VARCHAR)
	SET  @o_xmlString = (SELECT @@ERROR AS '@errorCode', 'Failed to get database details.' AS '@errorMessage'
			FOR XML PATH ('response'), root(N'Api_GetDatabasesResponse'))
END CATCH
IF @o_xmlString IS NULL
	SET @o_xmlString = '<Api_GetDatabasesResponse/>'
IF OBJECT_ID('tempdb.dbo.#getIdaObjects') IS NOT NULL
    DROP TABLE #getIdaObjects
IF OBJECT_ID('tempdb.dbo.#ClientInstanceDetails') IS NOT NULL
	DROP TABLE #ClientInstanceDetails
SELECT @o_xmlString AS o_xmlString
GO

IF EXISTS (select * from GxQscripts where name = 'AppGetDBDatabaseList')
	delete from GxQscripts where name = 'AppGetDBDatabaseList'
GO

IF EXISTS (select * from GXDBVersions where aliasname='AppGetDBDatabaseList')
	delete from GXDBVersions where aliasname = 'AppGetDBDatabaseList'
GO

insert into GXDBVersions values(2, 'AppGetDBDatabaseList',  'v1.1.2.51.4.1', 'AppGetDBDatabaseList', 'v1.1.2.51.4.1')
GO

