--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET NOCOUNT ON

BEGIN TRY

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

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

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

--------- Insert your SQL statements here 
-- Query Name: Backup Job Statistics Operation
-- Description: Backup Success and failure statistics

DECLARE @TimeZoneBias   INT = DATEDIFF(SECOND, GETUTCDATE(), GETDATE())
 
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

DECLARE @nreleaseId INT =1
SET @nreleaseId =(SELECT releaseId from APP_Client WITH (NOLOCK) where id =2)

DECLARE @LastCollectionAttrName VARCHAR(256)

-- Check if any special group to consider for data collection.
-- Get the list of client group ids if exists
DECLARE @groupStr varchar(256);
DECLARE @groupStrName Nvarchar(MAX)='';
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @frequencyMode  NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
    SELECT @frequencyMode = mode from #MetricsInputParams
END
IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning')
BEGIN
    SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'
END

IF ((@isPrivateRunning = 'Metrics Reporting')OR  (@frequencyMode ='1'))
BEGIN
    SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyPrivateSpecialClientGroup'
    SET @LastCollectionAttrName = 'CommservSurveyPrivateLastRestoreJobsCollectionTime'
END
ELSE IF ((@isPrivateRunning = 'Metrics Direct Dip')OR  (@frequencyMode ='3'))
BEGIN
    SET @LastCollectionAttrName = 'CommservSurveyDirectDIPLastRestoreJobsCollectionTime'
END
ELSE
BEGIN
    SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveySpecialClientGroup'
    SET @LastCollectionAttrName = 'CommservSurveyPublicLastRestoreJobsCollectionTime'
END

SET @groupStr = @groupStr + ',';
declare @includeGroups table(groupID int);
DECLARE @endIndex integer=0
DECLARE @startIndex integer=0
DECLARE @groupId integer        

SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
  
WHILE(@endIndex IS NOT NULL AND @endIndex <> 0)
BEGIN
    SET @groupId = CAST(SUBSTRING(@groupStr,@startIndex,@endIndex-@startIndex) AS INT)
    if(@groupId > 0)
    BEGIN
        insert into @includeGroups values(@groupId);
        DECLARE @tmpStr NVARCHAR(100);
        SELECT @tmpStr = NAME FROM APP_ClientGroup WITH (NOLOCK) WHERE id = @groupId;
        IF(@tmpStr IS NOT NULL)
            SET @groupStrName = @groupStrName + ', ' + @tmpStr
    END
    SET @startIndex = @endIndex + 1
    SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
END

DECLARE @specialGroupExists INT  =0
DECLARE @includeClients TABLE (clientId INT);

IF EXISTS (SELECT id FROM app_clientgroup WITH (NOLOCK) join @includeGroups on id=groupID)
BEGIN
    SET @specialGroupExists = 1
    
    INSERT INTO @includeClients
    SELECT DISTINCT CGA.clientId 
    FROM @includeGroups CG INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CGA.clientGroupId = CG.groupID  
END

-- Get the last collection time
DECLARE @CurrCollectionDate DATE = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
DECLARE @LastCollectionDate DATE = DATEADD(DAY, -1, @CurrCollectionDate)
DECLARE @PrevCollectionDate DATE

IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
    DECLARE @PrevCollectionDateSTR VARCHAR(256)='';
    EXEC GetSurveyParamValue @LastCollectionAttrName, @PrevCollectionDateSTR OUTPUT
    IF @PrevCollectionDateSTR = ''
    BEGIN
        IF  DAY(GETDATE()) > 16
            SET @PrevCollectionDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
        ELSE
            SET @PrevCollectionDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
    END
    ELSE
        SELECT @PrevCollectionDate = CAST(@PrevCollectionDateSTR AS DATETIME)
END
ELSE
BEGIN
    IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName)
        SELECT @PrevCollectionDate = CAST(CAST(value AS NVARCHAR(256)) AS DATE) from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName
    ELSE
    BEGIN
        IF  DAY(GETDATE()) > 16
            SET @PrevCollectionDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
        ELSE
            SET @PrevCollectionDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
        
        SET @PrevCollectionDate = DATEADD(DAY, -1, @PrevCollectionDate)
    END
END

IF object_id('tempdb.dbo.#DailyTbl') is not null DROP TABLE #DailyTbl

CREATE TABLE #DailyTbl (DateVal DATE, DayBegin INT, DayEnd INT)

DECLARE @dtDay DATE = @CurrCollectionDate

DECLARE @fromDt DATETIME
DECLARE @toDt DATETIME

WHILE @dtDay > @PrevCollectionDate
BEGIN
    SET @fromDt = @dtDay
    SET @toDt = DATEADD(DAY, 1, @dtDay)

    IF OBJECT_ID('IsInvalidTimeManaged') IS NOT NULL
    BEGIN
        WHILE dbo.IsInvalidTimeManaged(@fromDt, @csTimeZone) = 1
        BEGIN
            SET @fromDt = DATEADD(HOUR, 1, @fromDt)
        END
        
        WHILE dbo.IsInvalidTimeManaged(@toDt, @csTimeZone) = 1
        BEGIN
            SET @toDt = DATEADD(HOUR, 1, @toDt)
        END
    END

    INSERT INTO #DailyTbl 
    VALUES (@dtDay, 
        dbo.GetUnixTime(dbo.LocalToUTCTime(CONVERT(DATETIME, @fromDt), @csTimeZone)), 
        dbo.GetUnixTime(dbo.LocalToUTCTime(CONVERT(DATETIME, @toDt), @csTimeZone)) - 1)

    SET @dtDay = DATEADD(DAY, -1, @dtDay)
END

IF object_id('tempdb.dbo.#DailyJobs') is not null DROP TABLE #DailyJobs

CREATE TABLE #DailyJobs
(
    JobEndDate DATE,
    JobCount INT,
    RstSizeMB BIGINT
)

IF (@specialGroupExists = 0)
BEGIN
    INSERT INTO #DailyJobs 
    SELECT D.DateVal,
        SUM(CASE WHEN R.status IN (1,3,14) THEN 1 ELSE 0 END) as JobCount,
        SUM(CAST(convert(real, R.totUnCompBytes_h/(1024.0*1024.0)) * 214748.3648*10000 + convert(real, R.totUnCompBytes_l/(1024.0*1024.0)) AS BIGINT)) AS RstSizeMB
    FROM #DailyTbl D
        INNER JOIN JMRestoreStats R WITH (NOLOCK) ON R.servEndTime >= D.DayBegin AND R.servEndTime <= D.DayEnd
        INNER JOIN JMJobStats J WITH (NOLOCK) ON J.jobId = R.jobId AND J.commcellId = R.commcellId
    WHERE R.opType IN (5, 40) AND (R.rstattributes & 256) = 0 AND J.subOpType <> 129
    GROUP BY D.DateVal
    ORDER BY D.DateVal

END 
ELSE
BEGIN
    INSERT INTO #DailyJobs 
    SELECT D.DateVal,
        SUM(CASE WHEN R.status IN (1,3,14) THEN 1 ELSE 0 END) as JobCount,
        SUM(CAST(convert(real, R.totUnCompBytes_h/(1024.0*1024.0)) * 214748.3648*10000 + convert(real, R.totUnCompBytes_l/(1024.0*1024.0)) AS BIGINT)) AS RstSizeMB
    FROM #DailyTbl D
        INNER JOIN JMRestoreStats R WITH (NOLOCK) ON R.servEndTime >= D.DayBegin AND R.servEndTime <= D.DayEnd
        INNER JOIN JMJobStats J WITH (NOLOCK) ON J.jobId = R.jobId AND J.commcellId = R.commcellId
        INNER JOIN APP_Application A WITH (NOLOCK) ON R.srcClientId= A.id
        INNER JOIN @includeClients C ON C.clientId = A.clientId
    WHERE R.opType IN (5, 40) AND (R.rstattributes & 256) = 0 AND J.subOpType <> 129
    GROUP BY D.DateVal
    ORDER BY D.DateVal
END
    
DECLARE @DailyStatsXML XML = (
    SELECT 
        DISTINCT CAST(D.DateVal as DATE) as '@JobEndDate',
        ISNULL(DJ.JobCount,0) as '@JobCount',
        ISNULL(DJ.RstSizeMB,0) as '@RstSizeMB'
    FROM #DailyTbl D
        LEFT OUTER JOIN #DailyJobs DJ ON D.DateVal = DJ.JobEndDate
FOR XML PATH ('DailyRstJobStats'))

DROP TABLE #DailyTbl
DROP TABLE #DailyJobs



SET @surveyXML = 
( SELECT @DailyStatsXML
    FOR XML PATH ('RestoreStatsXML') )
    
--------- END SURVEY QUERY ---------

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)
     
END TRY
BEGIN CATCH  
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH

--Update the last collection time
IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
BEGIN
    EXEC SetSurveyParamValue @LastCollectionAttrName, @LastCollectionDate
END
ELSE 
BEGIN
    IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName)
        UPDATE GXGlobalParam SET value = CAST(@LastCollectionDate as NVARCHAR(MAX)) where name = @LastCollectionAttrName
    ELSE
    BEGIN
        DECLARE @nstring NVARCHAR(MAX)
        IF(@nreleaseId >= 15 )
        BEGIN
            SET @nstring ='insert into GXGlobalParam values(''' + @LastCollectionAttrName + ''', ''' + CAST(@LastCollectionDate as NVARCHAR(MAX)) + ''', dbo.GetUnixTime(GetUTCDate()), 0)'
            EXEC sp_executesql @nstring
        END
        ELSE IF(@nreleaseId = 14 )
        BEGIN
            insert into GXGlobalParam (name, value) values(@LastCollectionAttrName, CAST(@LastCollectionDate as NVARCHAR(MAX)))
        END
    END
END

SET NOCOUNT OFF
--------- END - GENERATED CODE ---------