--- Please follow the below comments to insert SQL statements.
-- DDB Protected or not
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

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



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

--------- Insert your SQL statements here 
-- Query to get DDBs protected via subclient or not
-- Get the last data protection job from MMEntityProp table


declare @nreleaseId int

SET @nreleaseId =(SELECT releaseId from APP_Client  where id =2)

IF object_id('tempdb.dbo.#tmpActiveStores') is not null DROP TABLE #tmpActiveStores             
DECLARE @csTimeZone    NVARCHAR(1024) = ''
SELECT  @csTimeZone = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZone, '') = ''
BEGIN
    SELECT @csTimeZone = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @csTimeZone = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255)
END



create  table #tmpActiveStores(substoreId INT, sidbstoreid integer, lastJobId integer, appSize bigint, writeTime int, lastbackuptime datetime, hoursSinceNoBackup int, ClientId INT, DDBSubclientId INT)

INSERT  INTO #tmpActiveStores
SELECT  DISTINCT SUB.SubStoreId,S.SIDBStoreId, 0, 0, 0,
        CASE WHEN @nreleaseId = 14 THEN dbo.UTCToLocalStringTime(dbo.getDateTime(SUB.LastSnapTime), @csTimeZone) ELSE '' END,
        CASE WHEN @nreleaseId = 14 THEN DATEDIFF(hour,dbo.getdatetime(SUB.LastSnapTime),GETUTCDATE()) ELSE -1 END, SUB.ClientId, -1
FROM    IdxSIDBStore S
		INNER JOIN IdxSIDBSubStore SUB WITH (NOLOCK) ON SUB.SIDBStoreId = S.SIDBStoreId
        INNER JOIN archCopySIDBStore ACS WITH (NOLOCK) ON S.SIDBStoreId = ACS.SIDBStoreId
        INNER JOIN archGroupCopy AC WITH (NOLOCK) ON ACS.CopyId = AC.id AND (AC.dedupeFlags & (268435456)) = 0 -- Do not consider GDSP's
        LEFT OUTER JOIN (
        SELECT  AG.id 
        FROM    archGroup AG 
                LEFT OUTER JOIN APP_Application A
                ON AG.id IN (A.dataArchGrpID, A.logArchGrpID)
        WHERE   (AG.flags & 64) = 64 AND A.id IS NULL
        ) X ON AC.archGroupId = X.id
WHERE  (S.flags & 256) = 0 and (S.SealedTime = 0) AND S.FirstBackupTime > 0
   AND (AC.dedupeFlags & (33554432 | 67108864)) > 0
   AND X.id IS NULL



declare @nstring nvarchar(max)



DECLARE @ddbprotection XML

if ( @nreleaseId = 14 )
BEGIN

   set @nstring = N'UPDATE #tmpActiveStores
   set lastJobId = props.longlongVal
   from #tmpActiveStores T, mmentityprop props
   where T.sidbstoreid = props.EntityId
       and props.EntityType = 3 
       and props.propertyName = ''DDBLastBackupJobId''
       and props.longlongVal > 0'
  
  
END     
ELSE IF(@nreleaseId >=15)
BEGIN


    set @nstring = N'
    
DECLARE @csTimeZone    NVARCHAR(1024) = ''''
SELECT  @csTimeZone = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZone, '''') = ''''
BEGIN
    SELECT @csTimeZone = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @csTimeZone = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@csTimeZone, CHARINDEX('':'', @csTimeZone, CHARINDEX('':'', @csTimeZone, 0) + 1) + 1, 255)
END


    
   UPDATE #tmpActiveStores
   set lastJobId = substore.LastSnapJobId,
   lastBackupTime =CASE WHEN (substore.LastSnapTime >0) THEN dbo.UTCToLocalStringTime(dbo.getDateTime(substore.LastSnapTime), @csTimeZone) ELSE '''' END,
   hoursSinceNoBackup =CASE WHEN (substore.LastSnapTime >0) THEN DATEDIFF(hour,dbo.getdatetime(substore.LastSnapTime),GETUTCDATE()) ELSE -1 END
   FROM #tmpActiveStores S
   INNER JOIN IdxSIDBSubStore substore ON S.SubStoreId = substore.SubStoreId
  
   '

END

exec sp_executesql @nstring

UPDATE S
SET DDBSubclientId = AA.Id
FROM #tmpActiveStores S 
INNER JOIN App_application AA ON AA.clientId = S.ClientId
INNER JOIN App_subclientProp SP ON SP.componentNameId = AA.id
WHERE SP.attrName = 'DDB Backup' and attrVal = 1

UPDATE S SET appSize = B.totalUncompBytes, writeTime = B.totalWriteTime
FROM #tmpActiveStores S INNER JOIN JMBkpStats B ON S.lastJobId = B.jobId AND B.commCellId = 2

set @ddbprotection = (
select T.substoreid as '@SubStoreId'
	,T.sidbstoreid as '@SidbStoreId'
    , Store.SIDBStoreName as '@SidbStoreName'
    ,T.lastJobId as '@LastDDBBackupJobId'
	, T.DDBSubclientId as '@DDBSubclientId'
    ,T.appSIze as '@AppSize'
    ,T.writeTime as '@WriteTime'
    ,LEFT(o.list, LEN(o.list) -2) as '@SPName'
    ,lastbackuptime as '@LastBackupTime'
    , hoursSinceNoBackup as '@HoursSinceNoBackup',
    CASE WHEN Store.flags & 8192 = 8192 THEN 1 ELSE 0 END AS '@IsTransactionBased'
from #tmpActiveStores T
inner join IdxSIDBStore Store WITH (NOLOCK) on T.sidbstoreid = Store.SIDBStoreId
cross apply ( select AG.name + ' : '  as [text()]
from 
archCopySIDBStore SidbStore WITH (NOLOCK) 
inner join archGroupCopy AGC with (nolock) on SidbStore.CopyId = AGC.id and  Store.SIDBStoreId = SidbStore.SIDBStoreID
inner join archGroup AG with (nolock) on AG.id = AGC.archGroupId
FOR xml PATH ('') ) AS o(list)
FOR XML PATH ('DDBStore'))



SET @surveyXML = (
	SELECT

	@ddbprotection
	FOR XML PATH ('DDBProtection') 
	-- Replace <<RESULTS_NAME>> with specific name
)

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

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


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

--SELECT cast (@surveyXML as XML)

SET NOCOUNT OFF

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