--Name:- Live sync replication details
--Description:- Live sync replication details for health report

SET NOCOUNT ON

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @relId int
DECLARE @spLevel int

select @relId = releaseId from CommServ..APP_Client where id = 2
select @spLevel = MIN(HighestSP) from simInstalledPackages where ClientId = 2

IF (OBJECT_ID('tempdb.dbo.#tblLiveSyncDetails') IS NOT NULL)
BEGIN
	DROP TABLE #tblLiveSyncDetails
END

Create Table #tblLiveSyncDetails(
	SourceVMClientName          NVARCHAR(256) NULL,
    SourceVMHyperVisorType      INT NOT NULL,
    DestVMClientName            NVARCHAR(256) NULL,
    DestVMHyperVisorType        INT NOT NULL,
    LastRestoreTime             INTEGER,
    LastBackupTime              INTEGER,
    TaskId                      INT,
    TaskName                    NVARCHAR(256) NULL,
    SubclientId                 INT,
    SubClientName               NVARCHAR(256) NULL,
    Flags                       INT,
    Status                      INT,
    BackupSetId                 INT,
    BackupSetName               NVARCHAR(256) NULL,
    LastSyncedBkpJob            INT,
    VMSyncedTillTime            INTEGER,
    BkpJobsToSync               NVARCHAR(MAX) NULL,
    ValidationFailCount         INT,
    FailoverStatus              INT
)

DECLARE @sql NVARCHAR(MAX)

IF (@relId = 15)
BEGIN
    IF(@spLevel >= 12)
    BEGIN
        SET @sql =  'INSERT INTO #tblLiveSyncDetails
            SELECT 
                V.sourceName,
                0,
                V.destinationName,
                0,
                ISNULL(V.lastRestoreTime,0),
                V.lastBackupTime,
                V.taskId,
                ST.subTaskName,
                V.subclientId,
                SC.subClientName,
                V.flags,
                V.Status,
                V.backupsetId,
                BS.name,
                V.lastSyncedBkpJob,
                ISNULL(V.vmsyncedTillTime,0),
                V.bkpjobsToSync,
                ISNULL(V.ValidationFailCount,0),
                0
            FROM APP_VSAReplication V
            INNER JOIN TM_SubTask ST
            ON V.taskId = ST.taskId
            INNER JOIN APP_Application SC
            ON V.subclientId = SC.id
            INNER JOIN APP_BackupSetName BS
            ON V.backupsetId = BS.id'
    END
END
ELSE IF (@relId = 16)
BEGIN
    IF(@spLevel <= 2)
    BEGIN
        SET @sql =  'INSERT INTO #tblLiveSyncDetails
            SELECT 
                V.sourceName,
                0,
                V.destinationName,
                0,
                ISNULL(V.lastRestoreTime,0),
                V.lastBackupTime,
                V.taskId,
                ST.subTaskName,
                V.subclientId,
                SC.subClientName,
                V.flags,
                V.Status,
                V.backupsetId,
                BS.name,
                V.lastSyncedBkpJob,
                ISNULL(V.vmsyncedTillTime,0),
                V.bkpjobsToSync,
                0,
                0
            FROM APP_VSAReplication V
            INNER JOIN TM_SubTask ST
            ON V.taskId = ST.taskId
            INNER JOIN APP_Application SC
            ON V.subclientId = SC.id
            INNER JOIN APP_BackupSetName BS
            ON V.backupsetId = BS.id'
    END
    ELSE
    BEGIN
        SET @sql =  'INSERT INTO #tblLiveSyncDetails
            SELECT 
                V.sourceName,
                0,
                V.destinationName,
                0,
                ISNULL(V.lastRestoreTime, 0),
                V.lastBackupTime,
                V.taskId,
                ST.subTaskName,
                V.subclientId,
                SC.subClientName,
                V.flags,
                V.Status,
                V.backupsetId,
                BS.name,
                V.lastSyncedBkpJob,
                ISNULL(V.vmsyncedTillTime,0),
                V.bkpjobsToSync,
                ISNULL(V.ValidationFailCount,0),
                V.FailoverStatus
            FROM APP_VSAReplication V
            INNER JOIN TM_SubTask ST
            ON V.taskId = ST.taskId
            INNER JOIN APP_Application SC
            ON V.subclientId = SC.id
            INNER JOIN APP_BackupSetName BS
            ON V.backupsetId = BS.id'
    END
END

exec sp_executesql @sql

IF COL_LENGTH('App_Client', 'displayName') IS NOT NULL
BEGIN
	SET @sql = N'UPDATE T SET SourceVMClientName = C.displayName FROM #tblLiveSyncDetails T INNER JOIN App_Client C ON C.name = T.SourceVMClientName AND C.displayName IS NOT NULL'
	EXEC sp_executesql @sql

	SET @sql = N'UPDATE T SET DestVMClientName = C.displayName FROM #tblLiveSyncDetails T INNER JOIN App_Client C ON C.name = T.DestVMClientName AND C.displayName IS NOT NULL'
	EXEC sp_executesql @sql
END

SET @surveyXML = ( SELECT 	dbo.NormalizeForXML(SourceVMClientName)          '@SourceVMClientName',
    SourceVMHyperVisorType      '@SourceVMHyperVisorType',
    dbo.NormalizeForXML(DestVMClientName)            '@DestVMClientName',
    DestVMHyperVisorType        '@DestVMHyperVisorType',
    LastRestoreTime             '@LastRestoreTime',
    LastBackupTime              '@LastBackupTime',
    TaskId                      '@TaskId',
    dbo.NormalizeForXML(TaskName)                    '@TaskName',
    SubclientId                 '@SubclientId',
    dbo.NormalizeForXML(SubClientName)               '@SubClientName',
    Flags                       '@Flags',
    Status                      '@Status',
    BackupSetId                 '@BackupSetId',
    dbo.NormalizeForXML(BackupSetName)               '@BackupSetName',
    LastSyncedBkpJob            '@LastSyncedBkpJob',
    VMSyncedTillTime            '@VMSyncedTillTime',
    BkpJobsToSync               '@BkpJobsToSync',
    ValidationFailCount         '@ValidationFailCount',
    FailoverStatus              '@FailoverStatus' 
FROM #tblLiveSyncDetails
FOR XML PATH('LiveSyncVMDetails'))

IF (OBJECT_ID('tempdb.dbo.#tblLiveSyncDetails') IS NOT NULL)
BEGIN
	DROP TABLE #tblLiveSyncDetails
END

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

SET NOCOUNT OFF