

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/WR_SLA.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.
-- -----------------------------------------------------------------------------------------------------------
-- rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/WR_SLA.sp,v $ $Id: WR_SLA.sp,v 1.21.2.77.6.5 2021/07/15 19:39:09 junlu Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='WR_SLA')
	delete from GXDBVersions where aliasname = 'WR_SLA'
GO
print '... Creating Procedure: WR_SLA'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure WR_SLA
  @i_req XML,
  @i_userId INT,
  @tableOutput VARCHAR(MAX) = '',
  @i_flag INT = 0
AS
  DECLARE @o_SLA NVARCHAR(MAX);
SET NOCOUNT ON
CREATE TABLE #tempSLA (date datetime, clientId int, appId int, fullJobsOnly int, appTypeId int, status int, category int, lastSeenTime int, slaDays int, slaDaysLevel varchar(32))
CREATE TABLE #tempSLAClient (date datetime, days int, fullJobsOnly int, clientId int, isProtected int, category int)
CREATE TABLE #ClientFilter (clientId INT)
CREATE TABLE #ClientSLADays (clientId INT, clientGroupId INT, days INT)
CREATE TABLE #SubclientSLA (clientId INT, isVM INT, appTypeId INT, appId INT, days INT, slaDaysLevel VARCHAR(32), protected INT, category INT, lastCheckTime INT)
CREATE TABLE #BackupsetSLA (appTypeId INT, backupsetId INT, category INT, slaDays INT)
CREATE TABLE #ExcludedEntity (clientId INT, isVM INT, clientGroupId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT, planId INT,
			category INT, installedTime INT, lastProtectedTime INT, lastOnlineTime INT, lastOfflineTime INT, isOnline VARCHAR(8), slaDays INT, clientType VARCHAR(32),ExcludedReason NVARCHAR(MAX))
CREATE TABLE #ClientAggr (clientId INT, isVM INT, appTypeId INT, status INT, category INT, slaDays INT, slaDaysLevel VARCHAR(32),
			ProtectedSubclients INT, MissedSubclients INT, lastProtectedTime INT, lastOnlineTime INT, lastOfflineTime INT, isOnline VARCHAR(8), clientType VARCHAR(32))
CREATE TABLE #ClientGroupsString (clientId INT, clientGroups NVARCHAR(MAX))
CREATE TABLE #LastBackupJobs (clientId INT, appTypeId INT, appId INT,
			lastSuccJobId INT, lastSuccParentJobId INT, lastSuccJobCCId INT, lastSuccJobStart INT, lastSuccJobLevel INT,
			lastFailJobId INT, lastFailParentJobId INT, lastFailJobCCId INT, lastFailJobStart INT, lastFailJobLevel INT,
			lastFailJobStatus INT, failureErrorCode INT, failureReasons NVARCHAR(MAX),
			status INT, category INT, slaDays INT, slaDaysLevel VARCHAR(32))
CREATE TABLE #ProtectedSubclientList (
			client NVARCHAR(1024), clientGroup NVARCHAR(MAX), agentType NVARCHAR(1024), instance NVARCHAR(1024), backupset NVARCHAR(1024), subclient NVARCHAR(1024),
			lastProtectedTime BIGINT, lastBackupType NVARCHAR(128), slaDays INT, slaDaysLevel VARCHAR(32),
			clientId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT)
CREATE TABLE #MissedClientSorted (
			Id int identity(1,1),
			client NVARCHAR(1024), clientGroup NVARCHAR(MAX), categoryName VARCHAR(1024),
			lastProtectedTime BIGINT, lastOnlineTime BIGINT, lastOfflineTime BIGINT, isOnline VARCHAR(8), slaDays INT, slaDaysLevel VARCHAR(32),
			clientId INT, clientType VARCHAR(32))
CREATE TABLE #MissedBackupset (
			client NVARCHAR(1024), clientGroup NVARCHAR(MAX), agentType NVARCHAR(1024), instance NVARCHAR(1024), backupset NVARCHAR(1024),
			category VARCHAR(1024), lastProtectedTime BIGINT, slaDays INT,
			clientId INT, appTypeId INT, instanceId INT, backupsetId INT)
CREATE TABLE #MissedBackupsetSorted (
			client NVARCHAR(1024), clientGroup NVARCHAR(MAX), agentType NVARCHAR(1024), instance NVARCHAR(1024), backupset NVARCHAR(1024),
			category VARCHAR(1024), lastProtectedTime BIGINT, slaDays INT,
			clientId INT, appTypeId INT, instanceId INT, backupsetId INT)
CREATE TABLE #ProtectedSubclientSorted (
			Id int identity(1,1),
			client NVARCHAR(1024), clientGroup NVARCHAR(MAX), agentType NVARCHAR(1024), instance NVARCHAR(1024), backupset NVARCHAR(1024), subclient NVARCHAR(1024),
			lastProtectedTime BIGINT, lastBackupType NVARCHAR(128), slaDays INT, slaDaysLevel VARCHAR(32),
			clientId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT)
CREATE TABLE #MissedClientSubclientList (
			client NVARCHAR(1024), clientGroup NVARCHAR(MAX), agentType NVARCHAR(1024), instance NVARCHAR(1024), backupset NVARCHAR(1024), subclient NVARCHAR(1024),
			category NVARCHAR(1024), lastProtectedTime BIGINT, lastBackupType NVARCHAR(128),
			lastFailedJobId INT, lastFailedParentJobId INT, lastFailedTime BIGINT, lastFailedBackupType NVARCHAR(128), failureReasons NVARCHAR(MAX), slaDays INT, slaDaysLevel VARCHAR(32),
			clientId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT)
CREATE TABLE #MissedClientSubclientSorted (
			Id int identity(1,1),
			client NVARCHAR(1024), clientGroup NVARCHAR(MAX), agentType NVARCHAR(1024), instance NVARCHAR(1024), backupset NVARCHAR(1024), subclient NVARCHAR(1024),
			category NVARCHAR(1024), lastProtectedTime BIGINT, lastBackupType NVARCHAR(128),
			lastFailedJobId INT, lastFailedParentJobId INT, lastFailedTime BIGINT, lastFailedBackupType NVARCHAR(128), failureReasons NVARCHAR(MAX), slaDays INT, slaDaysLevel VARCHAR(32),
			clientId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT)
CREATE TABLE #NoJobClientSubclient (
			client NVARCHAR(1024), clientGroup NVARCHAR(MAX), agentType NVARCHAR(1024), instance NVARCHAR(1024), backupset NVARCHAR(1024), subclient NVARCHAR(1024),
			category NVARCHAR(1024), lastProtectedTime INT, slaDays INT, slaDaysLevel VARCHAR(32),
			clientId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT)
CREATE TABLE #NoJobClientSubclientSorted (
			Id int identity(1,1),
			client NVARCHAR(1024), clientGroup NVARCHAR(MAX), agentType NVARCHAR(1024), instance NVARCHAR(1024), backupset NVARCHAR(1024), subclient NVARCHAR(1024),
			category NVARCHAR(1024), lastProtectedTime BIGINT, slaDays INT, slaDaysLevel VARCHAR(32),
			clientId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT)
CREATE TABLE #ExcludedEntitySorted (
			Id int identity(1,1),
			categoryStr NVARCHAR(128),
			client NVARCHAR(1024), clientGroup NVARCHAR(MAX), agentType NVARCHAR(1024), instance NVARCHAR(1024), backupset NVARCHAR(1024), subclient NVARCHAR(1024), planName NVARCHAR(1024),
			installedTime BIGINT, lastProtectedTime BIGINT, lastOnlineTime BIGINT, lastOfflineTime BIGINT, isOnline VARCHAR(8), slaDays INT,
			clientId INT, clientType VARCHAR(32), clientGroupId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT,planId INT ,ExcludedReason NVARCHAR(MAX))
CREATE TABLE #tblSummary (
			status INT, numberOfClients INT)
CREATE TABLE #tblAgentSummary (
			agentType VARCHAR(256), status INT, numberOfClients INT)
CREATE TABLE #tblTrend (
			trendDate DATETIME, ProtectedClients INT, MissedClients INT, ExcludedClients INT)
DECLARE @tblTimeFrame			TABLE (days INT)
DECLARE	@tblColumnFilter		TABLE (columnName NVARCHAR(128), filterValue NVARCHAR(1024), filterCondition NVARCHAR(16))
DECLARE	@tblClientGroupFilter	TABLE (name NVARCHAR(1024))
DECLARE @tblCategoryName		TABLE (category INT, name NVARCHAR(1024))
DECLARE @tblCategoryStats		TABLE (category INT, counts INT)
DECLARE	@i_days					INT
DECLARE	@i_type					INT
DECLARE	@i_status				INT
DECLARE	@i_category				INT
DECLARE	@i_localeId				INT
DECLARE @i_pageSize				INT
DECLARE @i_pageNo				INT
DECLARE @i_orderBy				INT
DECLARE @i_sortDirection		INT
DECLARE @i_clientType			INT -- 0-All, 1-Servers, 2-Laptops, 4-VMs
DECLARE @i_fullJobsOnly			INT
DECLARE @i_countScheduledJobs	INT
DECLARE	@i_startDate			INT
DECLARE	@i_endDate				INT
DECLARE	@i_agentType			VARCHAR(256)
DECLARE	@i_columnFilter			NVARCHAR(MAX)
DECLARE	@i_clientGroupFilter	NVARCHAR(MAX)
SET @i_days						= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@days', 'int'), 0)
SET @i_type						= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@type', 'int'), 1)
SET @i_status					= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@status', 'int'), 0)
SET @i_category					= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@category', 'int'), 0)
SET @i_localeId					= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/localeId[1]/@localeId', 'int'), 0)
SET @i_pageSize					= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@pageSize', 'int'), 20)
SET @i_pageNo					= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@pageNo', 'int'), 1)
SET @i_orderBy					= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@orderBy', 'int'), 0)
SET @i_sortDirection			= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@sortDirection', 'int'), 0)
SET @i_clientType				= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@showAllSubclients', 'int'), 0)
SET @i_fullJobsOnly				= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@countFullJobsOnly', 'int'), 0)
SET @i_countScheduledJobs		= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@countScheduledJobs', 'int'), 0)
SET @i_startDate				= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/startDate[1]/@time', 'int'), 0)
SET @i_endDate					= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/endDate[1]/@time', 'int'), 0)
SET @i_agentType				= ISNULL(@i_req.value('/WebReport_SLAReportReq[1]/@agentType', 'VARCHAR(255)'), '')
SET @i_columnFilter				= CAST(@i_req.query('/WebReport_SLAReportReq[1]') AS NVARCHAR(MAX))
SET @i_clientGroupFilter		= CAST(@i_req.query('/WebReport_SLAReportReq[1]') AS NVARCHAR(MAX))
DECLARE	@l_columnName			NVARCHAR(128)
DECLARE	@l_filtervalue			NVARCHAR(1024)
DECLARE	@l_filterCondition		NVARCHAR(16)
DECLARE @l_likeStr				NVARCHAR(1024)
DECLARE @l_number				BIGINT = 0
DECLARE @l_totalRows			INT  = 0
DECLARE @l_threshold			INT  = 96
DECLARE @l_days					INT  = @i_days
DECLARE @dateAsOf				VARCHAR(64)
DECLARE	@AsOfDate				DATETIME = NULL
DECLARE	@lastDate				DATETIME
DECLARE	@fromDate				DATETIME
DECLARE	@toTime					INT = 0
DECLARE @filterHandle			INT  = 0
DECLARE @dateFormat				INT  = 0
DECLARE @nExcludedClients		INT  = 0
DECLARE @cgOpt					INT  = 0
DECLARE @DSTFlag				INT = 0
DECLARE @Bias					INT = 0
DECLARE @csTimeZoneId			INT = 0
DECLARE @csTimeZone				NVARCHAR(1024) = ''
SELECT  @csTimeZoneId = attrVal FROM APP_ClientProp WITH (NOLOCK) WHERE attrName = 'Commcell timezone Id' AND modified = 0
IF @csTimeZoneId = 0
	SELECT  @csTimeZoneId = attrVal FROM APP_ClientProp WITH (NOLOCK) WHERE componentNameId = 2 AND attrName = 'timezone Id' AND modified = 0
SELECT	@csTimeZone = TimeZoneStdName, @DSTFlag = DSTFlag, @Bias = Bias FROM SchedTimeZone WITH (NOLOCK) WHERE TimeZoneID = @csTimeZoneId
DECLARE @UseDefaultSLADays		INT = (CASE WHEN @i_days <= 0 AND @i_startDate <= 0 AND @i_endDate <= 0 THEN 1 ELSE 0 END)
DECLARE @UseCustomSLADays		INT = (CASE WHEN @i_days = -1 AND @i_startDate  > 0 AND @i_endDate  > 0 THEN 1 ELSE 0 END)
DECLARE @SpecifiedDays			INT = 30
DECLARE	@ClientGroupSLADays		TABLE (clientGroupId INT, days INT)
-- Only for EDC cells or exclude all EDC cells. No mixture. One capture time for all EDC cells.
DECLARE	@IgnoreEDCCommCells	INT = 0
DECLARE	@EDCDumpCaptureTime	INT = 0
DECLARE	@IsEDC				INT = 0
SELECT	@IgnoreEDCCommCells = CAST(value AS INT)
FROM	GXGlobalParam WITH (NOLOCK)
WHERE	name = 'IgnoreEDCCellsForSLA'
IF	@IgnoreEDCCommCells = 0
BEGIN
	SELECT	@EDCDumpCaptureTime = CAST(value AS INT)
	FROM	GXGlobalParam WITH (NOLOCK)
	WHERE	name = 'EDCDumpCaptureTime'
	IF	@EDCDumpCaptureTime > 0
		SET @IsEDC = 1
END
-- get MiniBrandId for CommCell brand name
DECLARE	@l_MiniBrandId	INT
SELECT	@l_MiniBrandId = CAST(value AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'MiniBrandId' AND modified = 0
IF @l_MiniBrandId IS NULL
	SET @l_MiniBrandId = (SELECT TOP 1 OemId FROM LicCsl WITH (NOLOCK) ORDER BY id DESC)
SELECT	@l_threshold = CAST(value AS INT)
FROM	GXGlobalParam WITH (NOLOCK)
WHERE	name = 'SLA threshold percentage'
DECLARE @userPreferences XML = (SELECT TOP 1 value FROM UMUserWebPrefs WITH (NOLOCK) WHERE userId = @i_userId)
IF	@userPreferences IS NOT NULL
BEGIN
	IF	@i_pageSize IS NULL
		SET @i_pageSize = ISNULL(@userPreferences.value('/userPreferences[1]/general[1]/@rowsPerPage', 'int'), 20)
	SET @dateFormat = ISNULL(@userPreferences.value('/userPreferences[1]/general[1]/@dateFormat', 'int'), 1)
END
IF	@IsEDC = 0 AND @UseCustomSLADays = 0
	SELECT @toTime = CAST(value AS INT) FROM GxGlobalParam WITH (NOLOCK) WHERE name = 'SLA updated time'
IF	@toTime > 0
SET @AsOfDate = DATEADD(SS, @toTime, '1970-01-01')
ELSE
BEGIN
	SET	@AsOfDate = (SELECT MAX(date) FROM RptSLAClient WITH(NOLOCK))
	IF	DATEAdd(DAY, 2, @AsOfDate) > GETDATE()
		SET @AsOfDate = GETDATE()
END
SET	@dateAsOf = CONVERT(DATETIME, @AsOfDate, 100)
SET	@dateAsOf = LEFT(@dateAsOf, 6) + ', ' + RIGHT(@dateAsOf, LEN(@dateAsOf)-6)
SET	@dateAsOf = LEFT(@dateAsOf, 3) + ' ' + LTRIM(RIGHT(@dateAsOf, LEN(@dateAsOf)-3))
SET	@dateAsOf = LEFT(@dateAsOf, LEN(@dateAsOf)-2) + ' ' + RIGHT(@dateAsOf, 2)
EXEC sp_xml_preparedocument @filterHandle OUTPUT, @i_columnFilter
INSERT	INTO @tblColumnFilter
SELECT	columnName, filterValue, filterCondition
FROM	OPENXML (@filterHandle, '/WebReport_SLAReportReq[1]/filters', 1)
WITH (	columnName		NVARCHAR(128)	'@columnName',
		filterValue		NVARCHAR(1024)	'@filterValue',
		filterCondition	NVARCHAR(16)	'@filterCondition' )
DECLARE ColumnFilterCur CURSOR FOR
SELECT	columnName, filtervalue, filterCondition
FROM	@tblColumnFilter
EXEC sp_xml_preparedocument @filterHandle OUTPUT, @i_clientGroupFilter
INSERT	INTO @tblClientGroupFilter
SELECT	val
FROM	OPENXML (@filterHandle, '/WebReport_SLAReportReq[1]/clientGroupFilter', 1)
WITH (	val NVARCHAR(255) )
DECLARE @CountVSAAndIDAs INT = 0
SELECT	@CountVSAAndIDAs = longVal
FROM	APP_ComponentProp WITH (NOLOCK)
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3324 AND modified = 0
IF @i_type = 3 -- Backupset SLA
BEGIN
	IF @UseDefaultSLADays = 1
	BEGIN
		SET @l_days = -1
		SET @lastDate = NULL
	END
	ELSE
	IF @UseCustomSLADays = 1
	BEGIN
SET @l_days	  = DATEDIFF(DAY, DATEADD(SS, @i_startDate, '1970-01-01'), DATEADD(SS, @i_endDate, '1970-01-01'))
SET	@lastDate = dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, @i_endDate, '1970-01-01'), @csTimeZone)
SET	@toTime   = DATEDIFF(SS, '1970-01-01', dbo.LocalToUTCDateTimeConversionManaged(DATEADD(DAY, 1, @lastDate), @csTimeZone))
	END
	EXEC RptGetBackupsetSLA @i_userId, @l_days, @i_fullJobsOnly, @lastDate
	IF EXISTS (SELECT 1 from @tblClientGroupFilter)
		DELETE	S
		FROM	#SubclientSLA S
				LEFT OUTER JOIN (
					SELECT	DISTINCT CGA.clientId
					FROM	@tblClientGroupFilter F
							INNER JOIN APP_ClientGroup CG ON F.name = CG.name
							INNER JOIN APP_ClientGroupAssoc CGA ON CG.id = CGA.clientGroupId
				) T ON S.clientId = T.clientId
		WHERE	T.clientId IS NULL
	GOTO RETURN_DATA
END
IF @UseDefaultSLADays = 1
BEGIN
	SELECT	@SpecifiedDays = longVal
	FROM	APP_ComponentProp WITH (NOLOCK)
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3300 AND modified = 0
	IF @SpecifiedDays > 0 SET @l_days = @SpecifiedDays
	ELSE SET @l_days = 30
	INSERT	INTO @ClientGroupSLADays
	SELECT	componentId, longVal
	FROM	APP_ComponentProp WITH (NOLOCK)
WHERE	componentType = 8 AND propertyTypeId = 3300 AND modified = 0
	IF EXISTS (SELECT * FROM @ClientGroupSLADays)
	BEGIN
		SET @l_days = -1
		INSERT INTO #ClientSLADays
		SELECT	T.clientId, T.clientGroupId, T.days
		FROM	(SELECT CG.days, CGA.clientId, CGA.clientGroupId, ROW_NUMBER() OVER (PARTITION BY CGA.clientId ORDER BY CG.days) AS rowId
				FROM	@ClientGroupSLADays CG INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CG.clientGroupId = CGA.clientGroupId) T
		WHERE	T.rowId = 1
	END
END
IF @IsEDC = 1
BEGIN
	SET	@lastDate = (
		SELECT	MAX(date)
		FROM	RptSLAClient S WITH (NOLOCK)
				INNER JOIN APP_Client CL WITH (NOLOCK) ON S.clientId = CL.id
				INNER JOIN APP_CommCell CC WITH (NOLOCK) ON CL.origCCId = CC.id AND CC.type >= 100)
	SET @fromDate = DATEADD(DD, -@l_days, @lastDate)
	INSERT INTO #tempSLA (date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime)
	SELECT	date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime
	FROM	RptSLA WITH (NOLOCK)
	WHERE	date > @fromDate AND fullJobsOnly = @i_fullJobsOnly
	INSERT INTO #tempSLAClient
	SELECT	date, days, fullJobsOnly, clientId, (status & 1), category
	FROM	RptSLAClient WITH (NOLOCK)
	WHERE	date = @lastDate AND days = @l_days AND fullJobsOnly = @i_fullJobsOnly AND status IN (1, 2)
END
ELSE
BEGIN
	IF @UseCustomSLADays = 1
	BEGIN
		DECLARE @tempDate DATETIME
SET @l_days	  = DATEDIFF(DAY, DATEADD(SS, @i_startDate, '1970-01-01'), DATEADD(SS, @i_endDate, '1970-01-01'))
SET	@lastDate = dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, @i_endDate, '1970-01-01'), @csTimeZone)
		IF  EXISTS (SELECT * FROM RptSLA WITH (NOLOCK) WHERE date >= @lastDate)
			SELECT @tempDate = MIN(date) FROM RptSLA WITH (NOLOCK) WHERE date >= @lastDate
		ELSE
			SELECT @tempDate = MAX(date) FROM RptSLAClient WITH (NOLOCK)
		IF  @tempDate IS NOT NULL
			SET @lastDate = DATEADD(HOUR, DATEPART(HOUR, @tempDate), @lastDate)
		IF  @lastDate > GETDATE() AND DATEPART(HOUR, GETDATE()) < DATEPART(HOUR, @tempDate) AND @l_days > 0
		BEGIN
			SET @lastDate = DATEADD(DAY, -1, @lastDate)
			SET @l_days -= 1
		END
	END
	ELSE
	BEGIN
		SET @lastDate = (SELECT MAX(date) FROM RptSLAClient WITH (NOLOCK))
	END
	IF @lastDate = (SELECT MAX(date) FROM RptSLAClient WITH (NOLOCK) WHERE days = @l_days)
	BEGIN
		INSERT	INTO #tempSLAClient (date, days, fullJobsOnly, clientId, isProtected, category)
		SELECT	date, days, fullJobsOnly, clientId, CASE status WHEN 1 THEN 1 WHEN 3 THEN 3 ELSE 0 END, category
		FROM	RptSLAClient WITH (NOLOCK)
		WHERE	fullJobsOnly = @i_fullJobsOnly AND (days = @l_days OR days = 0 AND status = 3)
		IF @UseDefaultSLADays = 1
		BEGIN
			UPDATE C SET days = ISNULL(D.days, @SpecifiedDays)
			FROM	#tempSLAClient C
					LEFT OUTER JOIN #ClientSLADays D ON C.clientId = D.clientId
			INSERT	INTO #tempSLA (date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime, slaDays, slaDaysLevel)
			SELECT	date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime, slaDays, slaDaysLevel
			FROM	RptSLASubclient WITH (NOLOCK)
			WHERE	fullJobsOnly = @i_fullJobsOnly
		END
		ELSE
		BEGIN
			INSERT	INTO #tempSLA (date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime, slaDays, slaDaysLevel)
			SELECT	T.date, T.clientId, T.appId, T.fullJobsOnly, T.appTypeId,
					CASE WHEN S.category IS NOT NULL THEN S.category WHEN T.category IN (4,9) THEN T.category ELSE 3 END,
					CASE WHEN S.category IS NOT NULL THEN S.category WHEN T.category IN (4,9) THEN T.category ELSE 3 END,
					ISNULL(S.lastSeenTime, T.lastSeenTime), @l_days, -1
			FROM	RptSLASubclient T WITH (NOLOCK)
					INNER JOIN #tempSLAClient C ON T.clientId = C.clientId
					LEFT OUTER JOIN RptSLA S WITH (NOLOCK) ON T.clientId = S.clientId AND T.fullJobsOnly = S.fullJobsOnly
						AND (CASE WHEN T.appTypeId = 106 THEN 0 ELSE T.appId END) = (CASE WHEN S.appTypeId = 106 THEN 0 ELSE S.appId END)
						AND S.date >= DATEADD(DAY, -@l_days, @lastDate) AND S.date <= @lastDate
						AND S.category IN (1,2,3,4,9,12,13)
			WHERE	T.fullJobsOnly = @i_fullJobsOnly AND T.category IN (1,2,3,4,9,12,13)
		END
	END
	ELSE
	BEGIN
		EXEC RptOneDaySLA @lastDate, @l_days
		IF @l_days = -1
		BEGIN
			DELETE #tempSLAClient WHERE days > 0
			UPDATE C SET days = ISNULL(D.days, @SpecifiedDays)
			FROM	#tempSLAClient C
					LEFT OUTER JOIN #ClientSLADays D ON C.clientId = D.clientId
		END
		ELSE
			DELETE #tempSLAClient WHERE days = -1
	END
	DELETE #tempSLA WHERE fullJobsOnly <> @i_fullJobsOnly OR category NOT IN (1,2,3,4,9,12,13)
	DELETE #tempSLAClient WHERE days > 0 AND fullJobsOnly <> @i_fullJobsOnly
	CREATE INDEX #tempSLAClient_ClientId_idx ON #tempSLAClient (clientId)
	CREATE INDEX #tempSLA_ClientId_AppId_idx ON #tempSLA (clientId, appId)
	IF @i_clientType > 0
	BEGIN
		IF @i_clientType = 4 -- VMs
			DELETE	#tempSLA WHERE appTypeId <> 106
		ELSE
		IF @i_clientType = 2 -- Laptops
			DELETE	S FROM #tempSLA S
					INNER JOIN APP_Client CL ON S.clientId = CL.id
			WHERE	(CL.status&0x1000) = 0
		ELSE
		IF @i_clientType = 1 -- Servers
			DELETE	S FROM #tempSLA S
					INNER JOIN APP_Client CL ON S.clientId = CL.id
			WHERE	S.appTypeId = 106 OR (CL.status&0x1000) > 0
		DELETE	C FROM #tempSLAClient C
				LEFT OUTER JOIN (SELECT DISTINCT clientId FROM #tempSLA) S ON C.clientId = S.clientId
		WHERE	S.clientId IS NULL AND isProtected <> 3
	END
	IF @i_days = -1 AND @i_startDate > 0 AND @i_endDate > 0
	BEGIN
		UPDATE	#tempSLA SET slaDays = @l_days, slaDaysLevel = -1
		INSERT	INTO #tempSLAClient
		SELECT	@lastDate, @l_days, 0, S.clientId, 0, 10
		FROM	(SELECT DISTINCT clientId FROM #tempSLA WHERE category IN (3, 4)) S
				LEFT OUTER JOIN #tempSLAClient C ON S.clientId = C.clientId
		WHERE	C.clientId IS NULL
	END
	DELETE #tempSLA FROM #tempSLAClient C WHERE #tempSLA.clientId = C.clientId AND (C.category = 1 AND #tempSLA.category IN (5,6,7,8,10,11) OR C.category IN (6, 10))
	UPDATE #tempSLAClient SET days = @l_days WHERE days <= 0
SET	@toTime   = DATEDIFF(SS, '1970-01-01', dbo.LocalToUTCDateTimeConversionManaged(DATEADD(DAY, 1, @lastDate), @csTimeZone))
	IF NOT EXISTS (SELECT 1 FROM #tempSLA WHERE date < @lastDate)
	BEGIN
		INSERT INTO #tempSLA
		SELECT	S.date, S.clientId, S.appId, S.fullJobsOnly, S.appTypeId, S.status,
				CASE WHEN S.category = 3 AND T.category IN (4,9) THEN T.category ELSE S.category END, S.lastSeenTime, T.slaDays, T.slaDaysLevel
		FROM	RptSLA S WITH (NOLOCK)
				INNER JOIN #tempSLA T ON S.clientId = T.clientId
					AND (CASE WHEN S.appTypeId = 106 THEN 0 ELSE S.appId END) = (CASE WHEN T.appTypeId = 106 THEN 0 ELSE T.appId END)
		WHERE	S.date >= DATEADD(DAY, -T.slaDays, @lastDate) AND S.date <= @lastDate
			AND S.fullJobsOnly = @i_fullJobsOnly AND S.category IN (1,2,3,4,9,12,13) AND T.category IN (1,2,3,4,9,12,13)
	END
	-- #define CV_COMPONENT_COMMCELL_SNAP_JOBS_QUALIFY_SLA 3303
	DECLARE @SnapJobsQualifySLA INT = 0
	SELECT	@SnapJobsQualifySLA = longVal
	FROM	APP_ComponentProp WITH (NOLOCK)
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3303 AND modified = 0
	IF	@SnapJobsQualifySLA = 1
		UPDATE #tempSLA SET status = 1, category = 1 WHERE category = 12
	UPDATE #tempSLA SET status = CASE WHEN category IN (1, 13) THEN 1 WHEN category IN (2,3,4,9,12) THEN 2 ELSE 3 END
END
IF	EXISTS (SELECT * FROM @tblClientGroupFilter)
BEGIN
	INSERT	INTO #ClientFilter
	SELECT	A.clientId
	FROM	@tblClientGroupFilter F, APP_ClientGroup CG WITH (NOLOCK), APP_ClientGroupAssoc A WITH (NOLOCK)
	WHERE	CG.name = F.name AND CG.id = A.clientGroupId
	INSERT	INTO #ClientFilter
	SELECT	VMclientId
	FROM	(
			SELECT	VMclientId, appId, ROW_NUMBER() OVER (PARTITION BY VMclientId ORDER BY servStartDate DESC) AS rowNo
			FROM	APP_VMProp V WITH (NOLOCK)
					INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.jobId = V.jobId AND B.commCellId = V.commCellId
			WHERE	V.attrName = 'vmStatus'
			) VM
			INNER JOIN APP_Application A WITH (NOLOCK) ON VM.appId = A.id
			INNER JOIN #ClientFilter C ON A.clientId = C.clientId
			LEFT OUTER JOIN #ClientFilter CF ON VM.VMclientId = CF.clientId
	WHERE	rowNo = 1 AND CF.clientId IS NULL
	SET @cgOpt = 1
END
-- Check user visibility
DECLARE @isUserAdmin INT = 0
EXEC sec_isUserAdmin @i_userId, @isUserAdmin OUTPUT
IF	@i_userId > 0 AND @isUserAdmin = 0 AND @cgOpt = 0
BEGIN
	CREATE TABLE #tblUserVisibleObject (clientGroupId INT, clientId INT)
	CREATE TABLE #tblUserVisibleClient (clientId INT)
	IF object_id('tempdb.dbo.#WR_SLA_userObject') IS NOT Null DROP TABLE #WR_SLA_userObject
create table  #WR_SLA_userObject ( [capabilities]  bigint	NOT null, [flag]		  int NOT null default 0, [commCellId]    int	NOT null default 2, [clientGroupId] int	NOT null default 0, [clientId]      int	NOT null default 0, [appTypeId]     int	NOT null default 0, [instanceId]    int	NOT null default 0, [backupsetId]   int	NOT null default 0, [subClientId]   int	NOT null default 0, [mediaAgentId]  int	NOT null default 0, [libraryId]     int	NOT null default 0, [archGroupId]   int	NOT null default 0, [vtContainerId] int	NOT null default 0, [vtPolicyId]    int	NOT null default 0, [vtShelfId]     int	NOT null default 0, [reviewSetId]   int	NOT null default 0, [querySetId]	  int	NOT null default 0, [downloadSetId] int	NOT null default 0, [ermId]   	  int	NOT null default 0, [legalHoldId]   int	NOT null default 0, [tagId]		  int	NOT null default 0, [cdPolicyId]    int	NOT null default 0, [complianceReportId]   int	NOT null default 0, [taskId]        int	NOT null default 0, [workflowId]    int	NOT null default 0, [logMonitoringPolicyId] int	NOT null default 0, [arrayId]       int	NOT null default 0, [cloudId]       int	NOT null default 0, [userGroupId]   int	NOT null default 0, [providerId]    int	NOT null default 0, [entityId1]     int	NOT null default 0, [entityId2]     int	NOT null default 0, [entityId3]     int	NOT null default 0, [entityId4]     int	NOT null default 0, [entityId5]     int	NOT null default 0, [entityId6]     int	NOT null default 0, [entityId7]     int	NOT null default 0, [entityId8]     int	NOT null default 0, [entityId9]     int	NOT null default 0  )
	EXEC sec_getUserObjects @i_userId, 2009, 0, '#WR_SLA_userObject'
	INSERT	INTO #tblUserVisibleObject
	SELECT	DISTINCT clientGroupId, clientId
	FROM	#WR_SLA_userObject WITH (NOLOCK)
	WHERE	(clientId > 0
			OR clientGroupId > 0
			OR commCellId > 0 AND appTypeId = 0 AND instanceId = 0 AND backupsetId = 0 AND subClientId = 0
				AND mediaAgentId = 0 AND libraryId = 0 AND archGroupId = 0 AND vtContainerId = 0 AND vtPolicyId = 0
				AND vtShelfId = 0 AND reviewSetId = 0 AND querySetId = 0 AND downloadSetId = 0 AND tagId = 0
				AND legalHoldId = 0 AND ermId = 0 AND cdPolicyId = 0 AND complianceReportId = 0 AND taskId = 0
				AND workflowId = 0 AND logMonitoringPolicyId = 0 AND arrayId = 0 AND cloudId = 0 AND userGroupId = 0
				AND providerId = 0 AND entityId1 = 0 AND entityId2 = 0 AND entityId3 = 0 AND entityId4 = 0
				AND entityId5 = 0 AND entityId6 = 0 AND entityId7 = 0 AND entityId8 = 0 AND entityId9 = 0
			)
	IF object_id('tempdb.dbo.#WR_SLA_userObject') IS NOT Null DROP TABLE #WR_SLA_userObject
	IF  NOT EXISTS (SELECT * FROM #tblUserVisibleObject WHERE clientGroupId = 0 AND clientId = 0)
	BEGIN
		INSERT	INTO #tblUserVisibleClient
		SELECT	CGA.clientId
		FROM	(SELECT clientGroupId FROM #tblUserVisibleObject WHERE clientGroupId > 0) U
				INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CGA.clientGroupId = U.clientGroupId
		UNION
			SELECT clientId FROM #tblUserVisibleObject WHERE clientId > 0
	END
	ELSE
	BEGIN
			INSERT	INTO #tblUserVisibleClient
			select id from APP_Client WITH (NOLOCK) where id <> 1
	END
	IF	EXISTS (SELECT * FROM #tblUserVisibleClient)
	BEGIN
		IF	EXISTS (SELECT * FROM @tblClientGroupFilter)
			DELETE	#ClientFilter
			FROM	#ClientFilter T
					LEFT OUTER JOIN #tblUserVisibleClient C ON T.clientId = C.clientId
			WHERE	C.clientId IS NULL
		ELSE
			INSERT	INTO #ClientFilter
			SELECT	clientId FROM #tblUserVisibleClient
	END
	DROP TABLE #tblUserVisibleObject
	DROP TABLE #tblUserVisibleClient
END
-- get status and category for all clients
IF @cgOpt = 1 OR @isUserAdmin = 0
BEGIN
	INSERT	INTO #ExcludedEntity
	SELECT	E.clientId, 0, E.clientGroupId, E.appTypeId, E.instanceId, E.backupsetId, E.appId,E.planId,
			E.category, 0, 0, -1, -1, 'N/A', @i_days, 'Server',''
	FROM	RptSLAExclusion E WITH (NOLOCK)
			INNER JOIN #ClientFilter F ON E.clientId = F.clientId
	INSERT	INTO #ExcludedEntity
	SELECT	S.clientId, 0, 0, 0, 0, 0, 0,0,
			S.category, 0, 0, -1, -1, 'N/A', @i_days, 'Server',''
	FROM	#tempSLAClient S
			INNER JOIN #ClientFilter F ON S.clientId = F.clientId
			LEFT OUTER JOIN RptSLAExclusion E WITH (NOLOCK) ON S.clientId = E.clientId
	WHERE	S.clientId = E.clientId AND S.date = @lastDate AND S.isProtected = 3 AND E.clientId IS NULL
END
ELSE
BEGIN
	INSERT	INTO #ExcludedEntity
	SELECT	E.clientId, 0, E.clientGroupId, E.appTypeId, E.instanceId, E.backupsetId, E.appId,E.planId,
			E.category, 0, 0, -1, -1, 'N/A', @i_days, 'Server',''
	FROM	RptSLAExclusion E WITH (NOLOCK)
	INSERT	INTO #ExcludedEntity
	SELECT	S.clientId, 0, 0, 0, 0, 0, 0,0,
			S.category, 0, 0, -1, -1, 'N/A', @i_days, 'Server',''
	FROM	#tempSLAClient S
			LEFT OUTER JOIN RptSLAExclusion E WITH (NOLOCK) ON S.clientId = E.clientId
	WHERE	S.clientId = E.clientId AND S.date = @lastDate AND S.isProtected = 3 AND E.clientId IS NULL
END
-- Do not exclude VMs if discovered by some VSA subclient or some other agent is also installed
IF	@i_status NOT IN (0, 3)
	DELETE	#ExcludedEntity
	FROM	#tempSLA S
	WHERE	#ExcludedEntity.clientId = S.clientId AND S.date = @lastDate AND S.status IN (1, 2)
UPDATE	#ExcludedEntity SET isVM = 1, clientType = 'VM'
FROM	APP_ClientProp AS CP WITH (NOLOCK)
WHERE	#ExcludedEntity.clientId = CP.componentNameId AND CP.attrName = 'Virtual Server Discovered Clients' AND CP.attrVal = '1'
UPDATE	#ExcludedEntity SET ExcludedReason = CP.stringVal
FROM	APP_ComponentProp AS CP WITH (NOLOCK)
WHERE	#ExcludedEntity.clientGroupId = CP.componentId  AND CP.componentType = 8 AND CP.propertyTypeId = 3326 AND CP.modified = 0
UPDATE	#ExcludedEntity SET ExcludedReason = CP.attrVal
FROM	APP_ClientProp AS CP WITH (NOLOCK)
WHERE	#ExcludedEntity.clientId = CP.componentNameId AND CP.attrName = 'Exclude From SLA Reason' AND CP.modified = 0
UPDATE	#ExcludedEntity SET ExcludedReason = CP.attrVal
FROM	APP_PlanProp AS CP WITH (NOLOCK)
WHERE	#ExcludedEntity.planId = CP.componentNameId AND CP.attrName = 'SLA option description'  AND CP.modified = 0
UPDATE	#ExcludedEntity SET ExcludedReason = CP.attrVal
FROM	APP_SubClientProp AS CP WITH (NOLOCK)
WHERE	#ExcludedEntity.appId = CP.componentNameId AND CP.attrName = 'Exclude From SLA Reason' AND CP.modified = 0
UPDATE	#ExcludedEntity
SET		installedTime = S.installedTime
FROM	(SELECT	EC.clientId, MIN(CP.created) AS installedTime
		FROM	APP_ClientProp CP  WITH (NOLOCK) INNER JOIN #ExcludedEntity EC ON CP.componentNameId = EC.clientId AND CP.created > 0
		GROUP BY EC.clientId
		) S
WHERE	#ExcludedEntity.clientId = S.clientId
UPDATE	#ExcludedEntity
SET		lastOnlineTime = CCR.LastOnlineTime, lastOfflineTime = CCR.lastOfflineTime,
		isOnline = CASE WHEN EC.category <> 6 AND CCR.LastOnlineTime > CCR.lastOfflineTime THEN 'Yes' ELSE 'No' END,
		clientType = 'Laptop'
FROM	#ExcludedEntity EC
		INNER JOIN APP_Client C WITH (NOLOCK) ON C.id = EC.clientId AND (C.status & 0x1000) <> 0
		INNER JOIN CCRClientToClient CCR WITH (NOLOCK) ON CCR.ToClientID = C.id AND CCR.FromClientID = 2
IF @cgOpt = 1 OR @isUserAdmin = 0
	INSERT	INTO #SubclientSLA
	SELECT	S.clientId, 0, S.appTypeId, CASE WHEN S.appTypeId = 106 THEN 0 ELSE S.appId END AS appId, S.slaDays, S.slaDaysLevel,
			SUM(CASE WHEN S.status = 1 THEN 1 ELSE 0 END) AS Protected,
			CASE WHEN MIN(S.category) = 1 THEN 1 WHEN MIN(S.category%12) = 0 THEN 12 ELSE MIN(S.category%12) END AS Category,
			MAX(CASE WHEN S.category = 13 THEN lastSeenTime ELSE 0 END)
	FROM	#tempSLA S
			INNER JOIN #tempSLAClient C ON S.clientId = C.clientId
			INNER JOIN #ClientFilter F ON S.clientId = F.clientId
			INNER JOIN APP_iDAType I WITH (NOLOCK) ON S.appTypeId = I.type AND (@i_agentType = '' OR I.displayName = @i_agentType)
	WHERE	S.date >= DATEADD(DAY, -S.slaDays, @lastDate) AND S.appId > 0 AND S.status IN (1, 2) AND (C.isProtected = 0 OR S.status = 1)
	GROUP BY S.clientId, S.appTypeId, CASE WHEN S.appTypeId = 106 THEN 0 ELSE S.appId END, S.slaDays, S.slaDaysLevel
ELSE
	INSERT	INTO #SubclientSLA
	SELECT	S.clientId, 0, S.appTypeId, CASE WHEN S.appTypeId = 106 THEN 0 ELSE S.appId END AS appId, S.slaDays, S.slaDaysLevel,
			SUM(CASE WHEN S.status = 1 THEN 1 ELSE 0 END) AS Protected,
			CASE WHEN MIN(S.category) = 1 THEN 1 WHEN MIN(S.category%12) = 0 THEN 12 ELSE MIN(S.category%12) END AS Category,
			MAX(CASE WHEN S.category = 13 THEN lastSeenTime ELSE 0 END)
	FROM	#tempSLA S
			INNER JOIN #tempSLAClient C ON S.clientId = C.clientId
			INNER JOIN APP_iDAType I WITH (NOLOCK) ON S.appTypeId = I.type AND (@i_agentType = '' OR I.displayName = @i_agentType)
	WHERE	S.date >= DATEADD(DAY, -S.slaDays, @lastDate) AND S.appId > 0 AND S.status IN (1, 2) AND (C.isProtected = 0 OR S.status = 1)
	GROUP BY S.clientId, S.appTypeId, CASE WHEN S.appTypeId = 106 THEN 0 ELSE S.appId END, S.slaDays, S.slaDaysLevel
IF @IsEDC = 0
	DELETE	T
	FROM	#SubclientSLA T LEFT OUTER JOIN (
			SELECT	clientId, appTypeId, CASE WHEN appTypeId = 106 THEN 0 ELSE appId END AS appId
			FROM	#tempSLA
			WHERE	date = @lastDate AND appId > 0 AND status <> 3
			) W	ON W.clientId = T.clientId AND W.appTypeId = T.appTypeId AND W.appId = T.appId
	WHERE	W.clientId IS NULL
DROP TABLE #tempSLA
DROP TABLE #tempSLAClient
UPDATE	#SubclientSLA SET isVM = 1
FROM	APP_ClientProp AS CP WITH (NOLOCK)
WHERE	#SubclientSLA.clientId = CP.componentNameId AND CP.attrName = 'Virtual Server Discovered Clients' AND CP.attrVal = '1' AND CP.modified = 0
IF @CountVSAAndIDAs = 0
BEGIN
	-- Ignore other agents if VMs are protected by VSA
	DELETE	T FROM #SubclientSLA T
			INNER JOIN (
				SELECT DISTINCT clientId FROM #SubclientSLA WHERE isVM = 1 AND appTypeId = 106 AND category = 1
			) S ON T.clientId = S.clientId
	WHERE	T.isVM = 1 AND T.appTypeId <> 106 AND T.category <> 1
	-- Ignore VSA if VMs are protected by other agents
	DELETE	T FROM #SubclientSLA T
			INNER JOIN (
				SELECT clientId, COUNT(DISTINCT appId) AS nSub FROM #SubclientSLA WHERE isVM = 1 AND appTypeId <> 106 AND category = 1 GROUP BY clientId
			) S ON T.clientId = S.clientId
			INNER JOIN (
				SELECT clientId, COUNT(DISTINCT appId) AS nSub FROM #SubclientSLA WHERE isVM = 1 AND appTypeId <> 106 AND category IN (1, 2, 3, 4, 12) GROUP BY clientId
			) A ON T.clientId = A.clientId
	WHERE	T.isVM = 1 AND T.appTypeId = 106 AND T.category <> 1 AND S.nSub = A.nSub AND S.nSub > 0
END
IF @i_countScheduledJobs = 1
	UPDATE #SubclientSLA SET category = 1 WHERE category = 3
INSERT	INTO #ClientAggr
SELECT	T.clientId, CASE WHEN T.appTypeId = 106 THEN 1 ELSE 0 END, CASE WHEN @i_type = 2 THEN T.appTypeId ELSE 0 END,
		2, MIN(CASE T.Category WHEN 1 THEN 100 WHEN 12 THEN 99 ELSE T.Category END),
		MIN(T.days), 'CommCell',
		SUM(CASE WHEN T.Category = 1 THEN 1 ELSE 0 END),
		SUM(CASE WHEN T.Category = 0 THEN 1 ELSE 0 END),
		0, -1, -1, 'N/A', CASE WHEN isVM = 1 THEN 'VM' ELSE 'Server' END
FROM	#SubclientSLA T
GROUP BY T.clientId, CASE WHEN T.appTypeId = 106 THEN 1 ELSE 0 END, CASE WHEN @i_type = 2 THEN T.appTypeId ELSE 0 END, CASE WHEN isVM = 1 THEN 'VM' ELSE 'Server' END
UPDATE	C SET slaDays = L.days, slaDaysLevel = 'Client Group'
FROM	#ClientAggr C
		INNER JOIN (
			SELECT DISTINCT clientId, days FROM #SubclientSLA WHERE slaDaysLevel = 'Client Group'
		) L ON C.clientId = L.clientId
UPDATE	C SET slaDays = L.days, slaDaysLevel = 'Client Plan'
FROM	#ClientAggr C
		INNER JOIN (
			SELECT DISTINCT clientId, days FROM #SubclientSLA WHERE slaDaysLevel = 'Client Plan'
		) L ON C.clientId = L.clientId
UPDATE	#ClientAggr
SET		clientType = 'Laptop'
FROM	#ClientAggr T
		INNER JOIN APP_Client C WITH (NOLOCK) ON C.id = T.clientId AND (C.status & 0x1000) <> 0
UPDATE	#ClientAggr SET status = 1, category = 1 WHERE Category = 100
UPDATE	#ClientAggr SET category = 12 WHERE Category = 99
DELETE	#ClientAggr
FROM	#ExcludedEntity
WHERE	#ExcludedEntity.clientId = #ClientAggr.clientId AND #ExcludedEntity.appTypeId = 0
DELETE	#ClientAggr
FROM	#ClientAggr T
		INNER JOIN APP_Client CL WITH (NOLOCK) ON T.clientId = CL.id
		INNER JOIN APP_CommCell CC WITH (NOLOCK) ON CL.origCCId = CC.id
WHERE	(@IsEDC = 1 AND CC.type < 100 OR @IsEDC = 0 AND CC.type >= 100)
IF @CountVSAAndIDAs > 0
BEGIN
	DELETE	#ClientAggr
	FROM	#ClientAggr C
			INNER JOIN (SELECT clientId FROM #ClientAggr WHERE isVM = 0 AND status = 2) T ON C.clientId = T.clientId AND C.isVM = 1 AND status = 1
	DELETE	#ClientAggr
	FROM	#ClientAggr C
			INNER JOIN (SELECT clientId FROM #ClientAggr WHERE isVM = 1 AND status = 2) T ON C.clientId = T.clientId AND C.isVM = 0 AND status = 1
END
DELETE	#ExcludedEntity
FROM	#ExcludedEntity T
		INNER JOIN APP_Client CL WITH (NOLOCK) ON T.clientId = CL.id
		INNER JOIN APP_CommCell CC WITH (NOLOCK) ON CL.origCCId = CC.id
WHERE	(@IsEDC = 1 AND CC.type < 100 OR @IsEDC = 0 AND CC.type >= 100)
SET	@nExcludedClients = (SELECT COUNT(CL.id) FROM #ExcludedEntity EC INNER JOIN APP_Client CL WITH (NOLOCK)
		ON CL.id = EC.clientId AND EC.appTypeId = 0 AND (EC.isVM = 1 OR (CL.specialClientFlags & 1) = 0))
RETURN_DATA:
-- First page
IF	@i_status = 0
BEGIN
	INSERT	INTO @tblTimeFrame
	SELECT	1  UNION
	SELECT	2  UNION
	SELECT	3  UNION
	SELECT	5  UNION
	SELECT	7  UNION
	SELECT	14 UNION
	SELECT	30 UNION
	SELECT	longVal
	FROM	APP_ComponentProp WITH (NOLOCK)
	WHERE	(componentType = 1 AND componentId = 2 OR componentType = 8)
AND propertyTypeId = 3300 AND modified = 0
	INSERT	INTO #tblSummary
	SELECT	status, COUNT(DISTINCT clientId)
	FROM	#ClientAggr
	GROUP BY status
	INSERT	INTO #tblSummary
	SELECT	3, COUNT(*)
	FROM	#ExcludedEntity
	WHERE	category <> 7
	IF	@i_type = 2	-- ByAgentType
		INSERT	INTO #tblAgentSummary
		SELECT	I.displayName, C.status, COUNT(DISTINCT C.clientId)
		FROM	#ClientAggr C INNER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = C.appTypeId AND I.type > 0
		GROUP BY I.displayName, C.status
	ELSE
	IF	@i_type = 3 -- Backupset SLA
		INSERT	INTO #tblAgentSummary
		SELECT	I.displayName, CASE WHEN B.category = 1 THEN 1 ELSE 2 END, COUNT(DISTINCT B.backupsetId)
		FROM	#BackupsetSLA B INNER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = B.appTypeId AND I.type > 0
		GROUP BY I.displayName, CASE WHEN B.category = 1 THEN 1 ELSE 2 END
	ELSE
	BEGIN
		INSERT	INTO #tblTrend
		SELECT	date,
 				nClientsMet,
				nClientsMissed,
				nClientsExcluded
		FROM	RptSLAHistory WITH (NOLOCK)
		WHERE	days = (CASE WHEN @l_days IN (-1,1,2,3,5,7,14,30) THEN @l_days WHEN @l_days > 21 THEN 30 WHEN @l_days > 10 THEN 14 WHEN @l_days > 7 THEN 7 WHEN @l_days > 5 THEN 5 ELSE 3 END)
			AND fullJobsOnly = @i_fullJobsOnly
		DECLARE	@minDate DATETIME
		DECLARE	@numDate INT = 0
		SELECT	@minDate = MIN(trendDate), @numDate = 84 - COUNT(*)
		FROM	#tblTrend
		WHILE	@numDate > 0
		BEGIN
			INSERT INTO #tblTrend
			SELECT DATEADD(DAY, -@numDate, @minDate), 0, 0, 0
			SET @numDate -= 1
		END
	END
END
ELSE
BEGIN
	INSERT	INTO #ClientGroupsString
	SELECT	DISTINCT ClientId, 0
	FROM	#SubclientSLA
	CREATE INDEX ClientGroupsString_clientId_idx ON #ClientGroupsString (clientId)
	EXEC GetClientGroupsString
	UPDATE	#ClientGroupsString SET ClientGroups = CG.name
	FROM	#ClientGroupsString T
			INNER JOIN #ClientSLADays CL ON T.ClientId = CL.clientId
			INNER JOIN APP_ClientGroup CG WITH (NOLOCK) ON CL.clientGroupId = CG.id
END
-- Drill down to Protected Clients: status = 1 (protected) and category = 0 (Any)
IF	@i_status = 1
BEGIN
	IF	@i_type = 3 -- Backupset SLA
		INSERT INTO #LastBackupJobs
		SELECT	S.clientId, S.appTypeId, S.appId, 0, 0, 0, S.lastCheckTime, 0, 0, 0, 0, 0, 0, 0, 0, '',
				CASE WHEN S.category = 1 THEN 1 ELSE 2 END, S.category, S.days, S.slaDaysLevel
		FROM	#SubclientSLA S
				INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id
				INNER JOIN #BackupsetSLA B ON A.backupset = B.backupsetId
		WHERE	B.category = 1
	ELSE
		INSERT INTO #LastBackupJobs
		SELECT	DISTINCT S.clientId, S.appTypeId, S.appId, 0, 0, 0, S.lastCheckTime, 0, 0, 0, 0, 0, 0, 0, 0, '',
				CASE WHEN S.category = 1 THEN 1 ELSE 2 END, S.category, S.days, S.slaDaysLevel
		FROM	#SubclientSLA S
				INNER JOIN #ClientAggr C ON S.clientId = C.clientId AND (S.appTypeId = 106 AND C.isVM = 1 OR S.appTypeId <> 106 AND C.isVM = 0)
		WHERE	C.status = 1
	EXEC RptGetLastBackupJobs 1, @i_fullJobsOnly, @IsEDC, @i_localeId, @toTime
	INSERT	INTO #ProtectedSubclientList
	SELECT	CL.displayName,
			ISNULL(CG.ClientGroups, ''),
			CASE WHEN A.appTypeId = 106 THEN IDA.name + ' in ' + (SELECT name FROM APP_Client WITH (NOLOCK) WHERE id = A.clientId) ELSE IDA.name END,
			dbo.FixInstanceName(INS.name, A.appTypeId),
			BS.name,
			A.subclientName,
CASE WHEN S.lastSuccJobStart <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, S.lastSuccJobStart, '1970-01-01'), @csTimeZone)) END,
			dbo.GetJobBackupTypeString(@i_localeId, S.lastSuccJobLevel, 0),
			S.slaDays, S.slaDaysLevel,
			S.clientId, A.appTypeId, A.instance, A.backupset, A.id
	FROM	#LastBackupJobs S
			INNER JOIN APP_Application A WITH (NOLOCK) ON A.id = S.appId
			INNER JOIN APP_Client CL WITH (NOLOCK) ON CL.id = S.clientId
			INNER JOIN APP_iDAType IDA WITH (NOLOCK) ON IDA.type = A.appTypeId
			INNER JOIN APP_InstanceName INS WITH (NOLOCK) ON INS.id = A.instance
			INNER JOIN APP_BackupsetNAme BS WITH (NOLOCK) ON BS.id = A.backupset
			LEFT OUTER JOIN #ClientGroupsString CG ON CG.ClientId = A.clientId
	IF	@i_sortDirection = 0
		INSERT	INTO #ProtectedSubclientSorted
		SELECT	*
		FROM	#ProtectedSubclientList
		ORDER BY	(CASE @i_orderBy
						WHEN 1	THEN clientGroup
						WHEN 2	THEN client
						WHEN 3	THEN agentType
						WHEN 4	THEN instance
						WHEN 5	THEN backupset
						WHEN 6	THEN subclient
						WHEN 9	THEN lastBackupType
						ELSE	''
					END) ASC,
					(CASE @i_orderBy
						WHEN 8  THEN lastProtectedTime
						WHEN 19 THEN slaDays
						ELSE 0
					END) ASC,
					(CASE @i_orderBy WHEN 2 THEN '' ELSE client END) ASC,
					(CASE @i_orderBy WHEN 3 THEN '' ELSE agentType END) ASC,
					(CASE @i_orderBy WHEN 4 THEN '' ELSE instance END) ASC,
					(CASE @i_orderBy WHEN 5 THEN '' ELSE backupset END) ASC,
					(CASE @i_orderBy WHEN 6 THEN '' ELSE subclient END) ASC
	ELSE
		INSERT	INTO #ProtectedSubclientSorted
		SELECT	*
		FROM	#ProtectedSubclientList
		ORDER BY	(CASE @i_orderBy
						WHEN 1	THEN clientGroup
						WHEN 2	THEN client
						WHEN 3	THEN agentType
						WHEN 4	THEN instance
						WHEN 5	THEN backupset
						WHEN 6	THEN subclient
						WHEN 9	THEN lastBackupType
						ELSE	''
					END) DESC,
					(CASE @i_orderBy
						WHEN 8  THEN lastProtectedTime
						WHEN 19 THEN slaDays
						ELSE 0
					END) DESC,
					(CASE @i_orderBy WHEN 2 THEN '' ELSE client END) DESC,
					(CASE @i_orderBy WHEN 3 THEN '' ELSE agentType END) DESC,
					(CASE @i_orderBy WHEN 4 THEN '' ELSE instance END) DESC,
					(CASE @i_orderBy WHEN 5 THEN '' ELSE backupset END) DESC,
					(CASE @i_orderBy WHEN 6 THEN '' ELSE subclient END) DESC
	DROP TABLE #ProtectedSubclientList
	OPEN	ColumnFilterCur
	FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
	WHILE   @@FETCH_STATUS = 0
	BEGIN
		SET	@l_likeStr = '%' + @l_filtervalue + '%'
		IF	@l_columnName = 'clientGroup'
			DELETE	#ProtectedSubclientSorted WHERE clientGroup NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'client'
			DELETE	#ProtectedSubclientSorted WHERE client NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'agentType'
			DELETE	#ProtectedSubclientSorted WHERE agentType NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'instance'
			DELETE	#ProtectedSubclientSorted WHERE instance NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'backupset'
			DELETE	#ProtectedSubclientSorted WHERE backupset NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'subclient'
			DELETE	#ProtectedSubclientSorted WHERE subclient NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'lastBackupType'
			DELETE	#ProtectedSubclientSorted WHERE lastBackupType NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'lastProtectedTime'
		BEGIN
			IF ISDATE(@l_filtervalue) = 1
			BEGIN
				SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
				IF @l_filterCondition = '='
					DELETE	#ProtectedSubclientSorted WHERE lastProtectedTime <> @l_number
				ELSE IF @l_filterCondition = '>'
					DELETE	#ProtectedSubclientSorted WHERE lastProtectedTime <= @l_number
				ELSE IF @l_filterCondition = '>='
					DELETE	#ProtectedSubclientSorted WHERE lastProtectedTime <  @l_number
				ELSE IF @l_filterCondition = '<'
					DELETE	#ProtectedSubclientSorted WHERE lastProtectedTime >= @l_number OR lastProtectedTime <= 0
				ELSE IF @l_filterCondition = '<='
					DELETE	#ProtectedSubclientSorted WHERE lastProtectedTime >  @l_number OR lastProtectedTime <= 0
			END
		END
		ELSE IF	@l_columnName = 'slaDays'
		BEGIN
			IF @l_filterCondition = 'LIKE'
				DELETE	#ProtectedSubclientSorted WHERE slaDays NOT LIKE @l_likeStr
			ELSE IF ISNUMERIC(@l_filtervalue) = 1
			BEGIN
				SET @l_number = CAST(@l_filtervalue AS INT)
				IF @l_filterCondition = '='
					DELETE	#ProtectedSubclientSorted WHERE slaDays <> @l_number
				ELSE IF @l_filterCondition = '>'
					DELETE	#ProtectedSubclientSorted WHERE slaDays <= @l_number
				ELSE IF @l_filterCondition = '>='
					DELETE	#ProtectedSubclientSorted WHERE slaDays <  @l_number
				ELSE IF @l_filterCondition = '<'
					DELETE	#ProtectedSubclientSorted WHERE slaDays >= @l_number OR slaDays <= 0
				ELSE IF @l_filterCondition = '<='
					DELETE	#ProtectedSubclientSorted WHERE slaDays >  @l_number OR slaDays <= 0
			END
		END
		FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
	END
	CLOSE	ColumnFilterCur
	DEALLOCATE ColumnFilterCur
	SELECT	@l_totalRows = COUNT(*) FROM #ProtectedSubclientSorted
	IF	@i_pageSize <= 0
		SET @i_pageSize = @l_totalRows
	ELSE
	IF	@i_pageNo > 1 AND @i_pageSize > 0
		DELETE	TOP (@i_pageSize*(@i_pageNo-1))
		FROM	#ProtectedSubclientSorted
END
-- Drill down to Not Protected Clients: status = 2 (Unprotected) and category = 0 (Any)
IF	@i_status = 2
BEGIN
	INSERT	INTO @tblCategoryName
	SELECT	0, 'N/A'
	DECLARE @iter INT = 1
	WHILE @iter <= 12
	BEGIN
		INSERT	INTO @tblCategoryName
		SELECT	@iter, dbo.WR_GetSLACategoryString(@i_localeId, @iter)
		SET @iter += 1
	END
	IF	@i_type = 3 -- Backupset SLA
	BEGIN
		INSERT	INTO @tblCategoryStats
		SELECT	category, COUNT(DISTINCT backupsetId)
		FROM	#BackupsetSLA
		WHERE	category IN (2,3,4,9,12)
		GROUP BY category
		INSERT INTO #LastBackupJobs
		SELECT	S.clientId, S.appTypeId, S.appId, 0, 0, 0, S.lastCheckTime, 0, 0, 0, 0, 0, 0, 0, 0, '',
				CASE WHEN S.category = 1 THEN 1 ELSE 2 END, S.category, S.days, S.slaDaysLevel
		FROM	#SubclientSLA S
				INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id
				INNER JOIN #BackupsetSLA B ON A.backupset = B.backupsetId
		WHERE	(@i_category = 0 AND B.category IN (2,3,4,9,12) OR B.category = @i_category) AND S.category IN (2,3,4,9,12)
	END
	ELSE
	BEGIN
		INSERT	INTO @tblCategoryStats
		SELECT	category, COUNT(DISTINCT clientId)
		FROM	#ClientAggr
		WHERE	status = 2
		GROUP BY category
		INSERT INTO #LastBackupJobs
		SELECT	DISTINCT S.clientId, S.appTypeId, S.appId, 0, 0, 0,  S.lastCheckTime, 0, 0, 0, 0, 0, 0, 0, 0, '',
				CASE WHEN S.category = 1 THEN 1 ELSE 2 END, S.category, S.days, S.slaDaysLevel
		FROM	#SubclientSLA S
				INNER JOIN #ClientAggr C ON S.clientId = C.clientId
		WHERE	C.status = 2 AND (@i_category = 0 OR C.category = @i_category) AND S.category IN (2,3,4,9,12)
	END
	IF	@i_category = 0 AND @i_type = 3 -- Backupset SLA
	BEGIN
		EXEC RptGetLastBackupJobs 1, @i_fullJobsOnly, @IsEDC, @i_localeId, @toTime
		INSERT	INTO #MissedBackupset
		SELECT	CL.displayName,
				ISNULL(CG.ClientGroups, ''),
				CASE WHEN T.appTypeId = 106 THEN I.name + ' in ' + (SELECT name FROM APP_Client WITH (NOLOCK) WHERE id = T.clientId) ELSE I.name END,
				dbo.FixInstanceName(INS.name, T.appTypeId),
				BS.name,
				CN.name,
CASE WHEN T.lastSuccJobStart <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, T.lastSuccJobStart, '1970-01-01'), @csTimeZone)) END,
				B.slaDays,
				T.clientId, T.appTypeId, T.instance, T.backupset
		FROM	(	SELECT	A.clientId, A.appTypeId, A.instance, A.backupset, MIN(L.lastSuccJobStart) AS lastSuccJobStart
					FROM	#LastBackupJobs L
							INNER JOIN APP_Application A WITH (NOLOCK) ON A.id = L.appId
					GROUP BY A.clientId, A.appTypeId, A.instance, A.backupset
				) T
				INNER JOIN #BackupsetSLA B ON B.backupsetId = T.backupset
				INNER JOIN APP_Client CL WITH (NOLOCK) ON CL.id = T.clientId
				INNER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = T.appTypeId
				INNER JOIN APP_InstanceName INS WITH (NOLOCK) ON INS.id = T.Instance
				INNER JOIN APP_BackupsetName BS WITH (NOLOCK) ON BS.id = T.backupset
				INNER JOIN @tblCategoryName CN ON CN.category = B.category
				LEFT OUTER JOIN #ClientGroupsString CG ON CG.ClientId = T.clientId
		IF	@i_sortDirection = 0
			INSERT	INTO #MissedBackupsetSorted
			SELECT	*
			FROM	#MissedBackupset
			ORDER BY	(CASE @i_orderBy
							WHEN 1	THEN clientGroup
							WHEN 2	THEN client
							WHEN 3	THEN agentType
							WHEN 4	THEN instance
							WHEN 5	THEN backupset
							WHEN 15	THEN category
							ELSE	''
						END) ASC,
						(CASE @i_orderBy WHEN 8 THEN lastProtectedTime WHEN 19 THEN slaDays ELSE 0 END) ASC,
						(CASE @i_orderBy WHEN 2 THEN '' ELSE client END) ASC,
						(CASE @i_orderBy WHEN 3 THEN '' ELSE agentType END) ASC,
						(CASE @i_orderBy WHEN 4 THEN '' ELSE instance END) ASC,
						(CASE @i_orderBy WHEN 5 THEN '' ELSE backupset END) ASC
		ELSE
			INSERT	INTO #MissedBackupsetSorted
			SELECT	*
			FROM	#MissedBackupset
			ORDER BY	(CASE @i_orderBy
							WHEN 1	THEN clientGroup
							WHEN 2	THEN client
							WHEN 3	THEN agentType
							WHEN 4	THEN instance
							WHEN 5	THEN backupset
							WHEN 15	THEN category
							ELSE	''
						END) DESC,
						(CASE @i_orderBy WHEN 8 THEN lastProtectedTime WHEN 19 THEN slaDays ELSE 0 END) DESC,
						(CASE @i_orderBy WHEN 2 THEN '' ELSE client END) DESC,
						(CASE @i_orderBy WHEN 3 THEN '' ELSE agentType END) DESC,
						(CASE @i_orderBy WHEN 4 THEN '' ELSE instance END) DESC,
						(CASE @i_orderBy WHEN 5 THEN '' ELSE backupset END) DESC
		DROP TABLE #MissedBackupset
		OPEN	ColumnFilterCur
		FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
		WHILE   @@FETCH_STATUS = 0
		BEGIN
			SET	@l_likeStr = '%' + @l_filtervalue + '%'
			IF	@l_columnName = 'clientGroup'
				DELETE	#MissedBackupsetSorted WHERE clientGroup NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'client'
				DELETE	#MissedBackupsetSorted WHERE client NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'agentType'
				DELETE	#MissedBackupsetSorted WHERE agentType NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'instance'
				DELETE	#MissedBackupsetSorted WHERE instance NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'backupset'
				DELETE	#MissedBackupsetSorted WHERE backupset NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'category'
				DELETE	#MissedBackupsetSorted WHERE category NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'lastProtectedTime'
			BEGIN
				IF ISDATE(@l_filtervalue) = 1
				BEGIN
					SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
					IF @l_filterCondition = '='
						DELETE	#MissedBackupsetSorted WHERE lastProtectedTime <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#MissedBackupsetSorted WHERE lastProtectedTime <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#MissedBackupsetSorted WHERE lastProtectedTime <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#MissedBackupsetSorted WHERE lastProtectedTime >= @l_number OR lastProtectedTime <= 0
					ELSE IF @l_filterCondition = '<='
						DELETE	#MissedBackupsetSorted WHERE lastProtectedTime >  @l_number OR lastProtectedTime <= 0
				END
			END
			ELSE IF	@l_columnName = 'slaDays'
			BEGIN
				IF @l_filterCondition = 'LIKE'
					DELETE	#MissedBackupsetSorted WHERE CAST(slaDays AS VARCHAR(10)) NOT LIKE @l_likeStr
				ELSE IF ISNUMERIC(@l_filtervalue) = 1
				BEGIN
					SET @l_number = CAST(@l_filtervalue AS INT)
					IF @l_filterCondition = '='
						DELETE	#MissedBackupsetSorted WHERE slaDays <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#MissedBackupsetSorted WHERE slaDays <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#MissedBackupsetSorted WHERE slaDays <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#MissedBackupsetSorted WHERE slaDays >= @l_number OR slaDays <= 0
					ELSE IF @l_filterCondition = '<='
						DELETE	#MissedBackupsetSorted WHERE slaDays >  @l_number OR slaDays <= 0
				END
			END
			FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
		END
		CLOSE	ColumnFilterCur
		DEALLOCATE ColumnFilterCur
		SELECT	@l_totalRows = COUNT(*) FROM #MissedBackupsetSorted
		IF	@i_pageSize <= 0
			SET @i_pageSize = @l_totalRows
		ELSE
		IF	@i_pageNo > 1 AND @i_pageSize > 0
			DELETE	TOP (@i_pageSize*(@i_pageNo-1))
			FROM	#MissedBackupsetSorted
	END
	ELSE
	IF	@i_category = 0
	BEGIN
		EXEC RptGetLastBackupJobs 1, @i_fullJobsOnly, @IsEDC, @i_localeId, @toTime
		UPDATE	#ClientAggr
		SET		lastProtectedTime	= T.lastProtectedTime
		FROM	(SELECT	clientId, MIN(lastSuccJobStart) AS lastProtectedTime
				FROM	#LastBackupJobs
				WHERE	status = 2
				GROUP BY clientId) T
		WHERE	#ClientAggr.clientId = T.clientId AND #ClientAggr.status = 2
		UPDATE	#ClientAggr
		SET		lastOnlineTime = CCR.LastOnlineTime, lastOfflineTime = CCR.lastOfflineTime,
				isOnline = CASE WHEN CCR.LastOnlineTime > CCR.lastOfflineTime THEN 'Yes' ELSE 'No' END
		FROM	#ClientAggr CA
				INNER JOIN APP_Client C WITH (NOLOCK) ON C.id = CA.clientId AND (C.status & 0x1000) <> 0
				INNER JOIN CCRClientToClient CCR WITH (NOLOCK) ON CCR.ToClientID = C.id AND CCR.FromClientID = 2
		WHERE	CA.status = 2
		IF	@i_sortDirection = 0
			INSERT	INTO #MissedClientSorted
			SELECT	CL.displayName, ISNULL(CG.ClientGroups, ''), CN.name,
CASE WHEN C.lastProtectedTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, C.lastProtectedTime, '1970-01-01'), @csTimeZone)) END,
CASE WHEN C.lastOnlineTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, C.lastOnlineTime, '1970-01-01'), @csTimeZone)) END,
CASE WHEN C.lastOfflineTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, C.lastOfflineTime, '1970-01-01'), @csTimeZone)) END,
					C.isOnline,
					C.slaDays,
					C.slaDaysLevel,
					C.clientId,
					C.clientType
			FROM	(SELECT DISTINCT clientId, category, lastProtectedTime, lastOnlineTime, lastOfflineTime, isOnline, slaDays, slaDaysLevel, clientType FROM #ClientAggr WHERE status = 2) C
					INNER JOIN APP_Client CL WITH (NOLOCK) ON CL.id = C.clientId
					INNER JOIN @tblCategoryName CN ON CN.category = C.category
					LEFT OUTER JOIN #ClientGroupsString CG ON CG.ClientId = C.clientId
			ORDER BY	(CASE @i_orderBy WHEN 1 THEN CG.ClientGroups WHEN 2 THEN CL.displayName WHEN 15 THEN CN.name WHEN 17 THEN C.isOnline WHEN 21 THEN C.clientType ELSE '' END) ASC,
						(CASE @i_orderBy WHEN 8 THEN C.lastProtectedTime WHEN 14 THEN C.lastOnlineTime WHEN 16 THEN C.lastOfflineTime WHEN 19 THEN C.slaDays ELSE 0 END) ASC,
						(CASE @i_orderBy WHEN 2 THEN '' ELSE CL.displayName END) ASC
		ELSE
			INSERT	INTO #MissedClientSorted
			SELECT	CL.displayName, ISNULL(CG.ClientGroups, ''), CN.name,
CASE WHEN C.lastProtectedTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, C.lastProtectedTime, '1970-01-01'), @csTimeZone)) END,
CASE WHEN C.lastOnlineTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, C.lastOnlineTime, '1970-01-01'), @csTimeZone)) END,
CASE WHEN C.lastOfflineTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, C.lastOfflineTime, '1970-01-01'), @csTimeZone)) END,
					C.isOnline,
					C.slaDays,
					C.slaDaysLevel,
					C.clientId,
					C.clientType
			FROM	(SELECT DISTINCT clientId, category, lastProtectedTime, lastOnlineTime, lastOfflineTime, isOnline, slaDays, slaDaysLevel, clientType FROM #ClientAggr WHERE status = 2) C
					INNER JOIN APP_Client CL WITH (NOLOCK) ON CL.id = C.clientId
					INNER JOIN @tblCategoryName CN ON CN.category = C.category
					LEFT OUTER JOIN #ClientGroupsString CG ON CG.ClientId = C.clientId
			ORDER BY	(CASE @i_orderBy WHEN 1 THEN CG.ClientGroups WHEN 2 THEN CL.displayName WHEN 15 THEN CN.name WHEN 17 THEN C.isOnline WHEN 21 THEN C.clientType ELSE '' END) DESC,
						(CASE @i_orderBy WHEN 8 THEN C.lastProtectedTime WHEN 14 THEN C.lastOnlineTime WHEN 16 THEN C.lastOfflineTime WHEN 19 THEN C.slaDays ELSE 0 END) DESC,
						(CASE @i_orderBy WHEN 2 THEN '' ELSE CL.displayName END) DESC
		OPEN	ColumnFilterCur
		FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
		WHILE   @@FETCH_STATUS = 0
		BEGIN
			SET	@l_likeStr = '%' + @l_filtervalue + '%'
			IF	@l_columnName = 'clientGroup'
				DELETE	#MissedClientSorted WHERE clientGroup NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'client'
				DELETE	#MissedClientSorted WHERE client NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'clientType'
				DELETE	#MissedClientSorted WHERE clientType NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'category'
				DELETE	#MissedClientSorted WHERE categoryName NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'lastProtectedTime'
			BEGIN
				IF ISDATE(@l_filtervalue) = 1
				BEGIN
					SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
					IF @l_filterCondition = '='
						DELETE	#MissedClientSorted WHERE lastProtectedTime <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#MissedClientSorted WHERE lastProtectedTime <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#MissedClientSorted WHERE lastProtectedTime <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#MissedClientSorted WHERE lastProtectedTime >= @l_number OR lastProtectedTime <= 0
					ELSE IF @l_filterCondition = '<='
						DELETE	#MissedClientSorted WHERE lastProtectedTime >  @l_number OR lastProtectedTime <= 0
				END
			END
			ELSE IF	@l_columnName = 'lastSeenTime'
			BEGIN
				IF ISDATE(@l_filtervalue) = 1
				BEGIN
					SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
					IF @l_filterCondition = '='
						DELETE	#MissedClientSorted WHERE lastOnlineTime <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#MissedClientSorted WHERE lastOnlineTime <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#MissedClientSorted WHERE lastOnlineTime <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#MissedClientSorted WHERE lastOnlineTime >= @l_number OR lastOnlineTime <= 0
					ELSE IF @l_filterCondition = '<='
						DELETE	#MissedClientSorted WHERE lastOnlineTime >  @l_number OR lastOnlineTime <= 0
				END
			END
			ELSE IF	@l_columnName = 'lastOfflineTime'
			BEGIN
				IF ISDATE(@l_filtervalue) = 1
				BEGIN
					SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
					IF @l_filterCondition = '='
						DELETE	#MissedClientSorted WHERE lastOfflineTime <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#MissedClientSorted WHERE lastOfflineTime <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#MissedClientSorted WHERE lastOfflineTime <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#MissedClientSorted WHERE lastOfflineTime >= @l_number OR lastOfflineTime <= 0
					ELSE IF @l_filterCondition = '<='
						DELETE	#MissedClientSorted WHERE lastOfflineTime >  @l_number OR lastOfflineTime <= 0
				END
			END
			ELSE IF	@l_columnName = 'isOnline'
				DELETE	#MissedClientSorted WHERE isOnline NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'slaDays'
			BEGIN
				IF @l_filterCondition = 'LIKE'
					DELETE	#ProtectedSubclientSorted WHERE slaDays NOT LIKE @l_likeStr
				ELSE IF ISNUMERIC(@l_filtervalue) = 1
				BEGIN
					SET @l_number = CAST(@l_filtervalue AS INT)
					IF @l_filterCondition = '='
						DELETE	#ProtectedSubclientSorted WHERE slaDays <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#ProtectedSubclientSorted WHERE slaDays <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#ProtectedSubclientSorted WHERE slaDays <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#ProtectedSubclientSorted WHERE slaDays >= @l_number OR slaDays <= 0
					ELSE IF @l_filterCondition = '<='
						DELETE	#ProtectedSubclientSorted WHERE slaDays >  @l_number OR slaDays <= 0
				END
			END
			FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
		END
		CLOSE	ColumnFilterCur
		DEALLOCATE ColumnFilterCur
		SELECT	@l_totalRows = COUNT(*) FROM #MissedClientSorted
		IF	@i_pageSize <= 0
			SET @i_pageSize = @l_totalRows
		ELSE
		IF	@i_pageNo > 1 AND @i_pageSize > 0
			DELETE	TOP (@i_pageSize*(@i_pageNo-1))
			FROM	#MissedClientSorted
	END
	ELSE
	-- Drill down to status = 2 (Unprotected) and category = 2 (Failed) or 3 (NoJobRun)
	IF	@i_category IN (2, 3)
	BEGIN
		EXEC RptGetLastBackupJobs 2, @i_fullJobsOnly, @IsEDC, @i_localeId, @toTime
		INSERT	INTO #MissedClientSubclientList
		SELECT	CL.displayName,
				ISNULL(CG.ClientGroups, ''),
				CASE WHEN A.appTypeId = 106 THEN IDA.name + ' in ' + (SELECT name FROM APP_Client WITH (NOLOCK) WHERE id = A.clientId) ELSE IDA.name END,
				dbo.FixInstanceName(INS.name, A.appTypeId),
				BS.name,
				A.subclientName,
				CN.name,
CASE WHEN S.lastSuccJobStart <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, S.lastSuccJobStart, '1970-01-01'), @csTimeZone)) END,
				CASE WHEN S.lastSuccJobLevel <= 0 THEN 'N/A' ELSE dbo.GetJobBackupTypeString(@i_localeId, S.lastSuccJobLevel, 0) END,
				S.lastFailJobId,
				S.lastFailParentJobId,
CASE WHEN S.lastFailJobStart <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, S.lastFailJobStart, '1970-01-01'), @csTimeZone)) END,
				CASE WHEN  S.lastFailJobLevel <= 0 THEN 'N/A' ELSE dbo.GetJobBackupTypeString(@i_localeId, S.lastFailJobLevel, 0) END,
				S.failureReasons,
				S.slaDays, S.slaDaysLevel,
				S.clientId, A.appTypeId, A.instance, A.backupset, A.id
		FROM	#LastBackupJobs S
				LEFT OUTER JOIN APP_Application A WITH (NOLOCK) ON A.id = S.appId
				INNER JOIN APP_Client CL WITH (NOLOCK) ON CL.id = S.clientId
				LEFT OUTER JOIN APP_iDAType IDA WITH (NOLOCK) ON IDA.type = A.appTypeId
				LEFT OUTER JOIN APP_InstanceName INS WITH (NOLOCK) ON INS.id = A.instance
				LEFT OUTER JOIN APP_BackupsetNAme BS WITH (NOLOCK) ON BS.id = A.backupset
				INNER JOIN @tblCategoryName CN ON CN.category = S.category
				LEFT OUTER JOIN #ClientGroupsString CG ON CG.ClientId = A.clientId
		IF	@i_sortDirection = 0
			INSERT	INTO #MissedClientSubclientSorted
			SELECT	*
			FROM	#MissedClientSubclientList
			ORDER BY	(CASE @i_orderBy
							WHEN 1	THEN clientGroup
							WHEN 2	THEN client
							WHEN 3	THEN agentType
							WHEN 4	THEN instance
							WHEN 5	THEN backupset
							WHEN 6	THEN subclient
							WHEN 9	THEN lastBackupType
							WHEN 12 THEN lastFailedBackupType
							WHEN 13 THEN failureReasons
							WHEN 15	THEN category
							ELSE	''
						END) ASC,
						(CASE @i_orderBy
							WHEN 8  THEN lastProtectedTime
							WHEN 10 THEN lastFailedJobId
							WHEN 11 THEN lastFailedTime
							WHEN 19 THEN slaDays
							WHEN 20 THEN lastFailedParentJobId
							ELSE 0
						END) ASC,
						(CASE @i_orderBy WHEN 2 THEN '' ELSE client END) ASC,
						(CASE @i_orderBy WHEN 3 THEN '' ELSE agentType END) ASC,
						(CASE @i_orderBy WHEN 4 THEN '' ELSE instance END) ASC,
						(CASE @i_orderBy WHEN 5 THEN '' ELSE backupset END) ASC,
						(CASE @i_orderBy WHEN 6 THEN '' ELSE subclient END) ASC
		ELSE
			INSERT	INTO #MissedClientSubclientSorted
			SELECT	*
			FROM	#MissedClientSubclientList
			ORDER BY	(CASE @i_orderBy
							WHEN 1	THEN clientGroup
							WHEN 2	THEN client
							WHEN 3	THEN agentType
							WHEN 4	THEN instance
							WHEN 5	THEN backupset
							WHEN 6	THEN subclient
							WHEN 9	THEN lastBackupType
							WHEN 12 THEN lastFailedBackupType
							WHEN 13 THEN failureReasons
							WHEN 15	THEN category
							ELSE	''
						END) DESC,
						(CASE @i_orderBy
							WHEN 8  THEN lastProtectedTime
							WHEN 10 THEN lastFailedJobId
							WHEN 11 THEN lastFailedTime
							WHEN 19 THEN slaDays
							WHEN 20 THEN lastFailedParentJobId
							ELSE 0
						END) DESC,
						(CASE @i_orderBy WHEN 2 THEN '' ELSE client END) DESC,
						(CASE @i_orderBy WHEN 3 THEN '' ELSE agentType END) DESC,
						(CASE @i_orderBy WHEN 4 THEN '' ELSE instance END) DESC,
						(CASE @i_orderBy WHEN 5 THEN '' ELSE backupset END) DESC,
						(CASE @i_orderBy WHEN 6 THEN '' ELSE subclient END) DESC
		DROP TABLE #MissedClientSubclientList
		OPEN	ColumnFilterCur
		FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
		WHILE   @@FETCH_STATUS = 0
		BEGIN
			SET	@l_likeStr = '%' + @l_filtervalue + '%'
			IF	@l_columnName = 'clientGroup'
				DELETE	#MissedClientSubclientSorted WHERE clientGroup NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'client'
				DELETE	#MissedClientSubclientSorted WHERE client NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'agentType'
				DELETE	#MissedClientSubclientSorted WHERE agentType NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'instance'
				DELETE	#MissedClientSubclientSorted WHERE instance NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'backupset'
				DELETE	#MissedClientSubclientSorted WHERE backupset NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'subclient'
				DELETE	#MissedClientSubclientSorted WHERE subclient NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'category'
				DELETE	#MissedClientSubclientSorted WHERE category NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'lastBackupType'
				DELETE	#MissedClientSubclientSorted WHERE lastBackupType NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'failureReasons'
				DELETE	#MissedClientSubclientSorted WHERE failureReasons NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'lastProtectedTime'
			BEGIN
				IF ISDATE(@l_filtervalue) = 1
				BEGIN
					SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
					IF @l_filterCondition = '='
						DELETE	#MissedClientSubclientSorted WHERE lastProtectedTime <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#MissedClientSubclientSorted WHERE lastProtectedTime <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#MissedClientSubclientSorted WHERE lastProtectedTime <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#MissedClientSubclientSorted WHERE lastProtectedTime >= @l_number OR lastProtectedTime <= 0
					ELSE IF @l_filterCondition = '<='
						DELETE	#MissedClientSubclientSorted WHERE lastProtectedTime >  @l_number OR lastProtectedTime <= 0
				END
			END
			ELSE IF	@l_columnName = 'lastFailedJobId'
			BEGIN
				IF @l_filterCondition = 'LIKE'
					DELETE	#MissedClientSubclientSorted WHERE CAST(lastFailedJobId AS VARCHAR(10)) NOT LIKE @l_likeStr
				ELSE IF ISNUMERIC(@l_filtervalue) = 1
				BEGIN
					SET @l_number = CAST(@l_filtervalue AS BIGINT)
					IF @l_filterCondition = '='
						DELETE	#MissedClientSubclientSorted WHERE lastFailedJobId <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#MissedClientSubclientSorted WHERE lastFailedJobId <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#MissedClientSubclientSorted WHERE lastFailedJobId <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#MissedClientSubclientSorted WHERE lastFailedJobId >= @l_number
					ELSE IF @l_filterCondition = '<='
						DELETE	#MissedClientSubclientSorted WHERE lastFailedJobId >  @l_number
				END
			END
			ELSE IF	@l_columnName = 'lastFailedBackupType'
				DELETE	#MissedClientSubclientSorted WHERE lastFailedBackupType NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'lastFailedTime'
			BEGIN
				IF ISDATE(@l_filtervalue) = 1
				BEGIN
					SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
					IF @l_filterCondition = '='
						DELETE	#MissedClientSubclientSorted WHERE lastFailedTime <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#MissedClientSubclientSorted WHERE lastFailedTime <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#MissedClientSubclientSorted WHERE lastFailedTime <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#MissedClientSubclientSorted WHERE lastFailedTime >= @l_number OR lastProtectedTime <= 0
					ELSE IF @l_filterCondition = '<='
						DELETE	#MissedClientSubclientSorted WHERE lastFailedTime >  @l_number OR lastProtectedTime <= 0
				END
			END
			ELSE IF	@l_columnName = 'slaDays'
			BEGIN
				IF @l_filterCondition = 'LIKE'
					DELETE	#MissedClientSubclientSorted WHERE CAST(slaDays AS VARCHAR(10)) NOT LIKE @l_likeStr
				ELSE IF ISNUMERIC(@l_filtervalue) = 1
				BEGIN
					SET @l_number = CAST(@l_filtervalue AS INT)
					IF @l_filterCondition = '='
						DELETE	#MissedClientSubclientSorted WHERE slaDays <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#MissedClientSubclientSorted WHERE slaDays <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#MissedClientSubclientSorted WHERE slaDays <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#MissedClientSubclientSorted WHERE slaDays >= @l_number OR slaDays <= 0
					ELSE IF @l_filterCondition = '<='
						DELETE	#MissedClientSubclientSorted WHERE slaDays >  @l_number OR slaDays <= 0
				END
			END
			ELSE IF	@l_columnName = 'lastFailedParentJobId'
			BEGIN
				IF @l_filterCondition = 'LIKE'
					DELETE	#MissedClientSubclientSorted WHERE CAST(lastFailedParentJobId AS VARCHAR(10)) NOT LIKE @l_likeStr
				ELSE IF ISNUMERIC(@l_filtervalue) = 1
				BEGIN
					SET @l_number = CAST(@l_filtervalue AS INT)
					IF @l_filterCondition = '='
						DELETE	#MissedClientSubclientSorted WHERE lastFailedParentJobId <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#MissedClientSubclientSorted WHERE lastFailedParentJobId <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#MissedClientSubclientSorted WHERE lastFailedParentJobId <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#MissedClientSubclientSorted WHERE lastFailedParentJobId >= @l_number
					ELSE IF @l_filterCondition = '<='
						DELETE	#MissedClientSubclientSorted WHERE lastFailedParentJobId >  @l_number
				END
			END
			FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
		END
		CLOSE	ColumnFilterCur
		DEALLOCATE ColumnFilterCur
		SELECT	@l_totalRows = COUNT(*) FROM #MissedClientSubclientSorted
		IF	@i_pageSize <= 0
			SET @i_pageSize = @l_totalRows
		ELSE
		IF	@i_pageNo > 1 AND @i_pageSize > 0
			DELETE	TOP (@i_pageSize*(@i_pageNo-1))
			FROM	#MissedClientSubclientSorted
	END
	-- Drill down to status = 2 (Unprotected) and category = 4 (NoSchedule) or 9 (FilteredVM) or 12 (SnapJobsWithNoBackupCopy)
	IF	@i_category IN (4, 9, 12)
	BEGIN
		EXEC RptGetLastBackupJobs 1, @i_fullJobsOnly, @IsEDC, @i_localeId, @toTime
		INSERT	INTO #NoJobClientSubclient
		SELECT	CL.displayName,
				ISNULL(CG.ClientGroups, ''),
				CASE WHEN A.appTypeId = 106 THEN I.name + ' in ' + (SELECT name FROM APP_Client WITH (NOLOCK) WHERE id = A.clientId) ELSE I.name END,
				dbo.FixInstanceName(INS.name, A.appTypeId),
				BS.name,
				A.subclientName,
				CN.name,
CASE WHEN T.lastSuccJobStart <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, T.lastSuccJobStart, '1970-01-01'), @csTimeZone)) END,
				T.slaDays, T.slaDaysLevel,
				T.clientId, A.appTypeId, A.instance, A.backupset, A.id
		FROM	#LastBackupJobs T
				LEFT OUTER JOIN APP_Application A WITH (NOLOCK) ON A.id = T.appId
				INNER JOIN APP_Client CL WITH (NOLOCK) ON CL.id = T.clientId
				LEFT OUTER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = A.appTypeId
				LEFT OUTER JOIN APP_InstanceName INS WITH (NOLOCK) ON INS.id = A.Instance
				LEFT OUTER JOIN APP_BackupsetName BS WITH (NOLOCK) ON BS.id = A.backupset
				INNER JOIN @tblCategoryName CN ON CN.category = T.category
				LEFT OUTER JOIN #ClientGroupsString CG ON CG.ClientId = A.clientId
		IF	@i_sortDirection = 0
			INSERT	INTO #NoJobClientSubclientSorted
			SELECT	*
			FROM	#NoJobClientSubclient
			ORDER BY	(CASE @i_orderBy
							WHEN 1	THEN clientGroup
							WHEN 2	THEN client
							WHEN 3	THEN agentType
							WHEN 4	THEN instance
							WHEN 5	THEN backupset
							WHEN 6	THEN subclient
							WHEN 15	THEN category
							ELSE	''
						END) ASC,
						(CASE @i_orderBy WHEN 8 THEN lastProtectedTime WHEN 19 THEN slaDays ELSE 0 END) ASC,
						(CASE @i_orderBy WHEN 2 THEN '' ELSE client END) ASC,
						(CASE @i_orderBy WHEN 3 THEN '' ELSE agentType END) ASC,
						(CASE @i_orderBy WHEN 4 THEN '' ELSE instance END) ASC,
						(CASE @i_orderBy WHEN 5 THEN '' ELSE backupset END) ASC,
						(CASE @i_orderBy WHEN 6 THEN '' ELSE subclient END) ASC
		ELSE
			INSERT	INTO #NoJobClientSubclientSorted
			SELECT	*
			FROM	#NoJobClientSubclient
			ORDER BY	(CASE @i_orderBy
							WHEN 1	THEN clientGroup
							WHEN 2	THEN client
							WHEN 3	THEN agentType
							WHEN 4	THEN instance
							WHEN 5	THEN backupset
							WHEN 6	THEN subclient
							WHEN 15	THEN category
							ELSE	''
						END) DESC,
						(CASE @i_orderBy WHEN 8 THEN lastProtectedTime WHEN 19 THEN slaDays ELSE 0 END) DESC,
						(CASE @i_orderBy WHEN 2 THEN '' ELSE client END) DESC,
						(CASE @i_orderBy WHEN 3 THEN '' ELSE agentType END) DESC,
						(CASE @i_orderBy WHEN 4 THEN '' ELSE instance END) DESC,
						(CASE @i_orderBy WHEN 5 THEN '' ELSE backupset END) DESC,
						(CASE @i_orderBy WHEN 6 THEN '' ELSE subclient END) DESC
		DROP TABLE #NoJobClientSubclient
		OPEN	ColumnFilterCur
		FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
		WHILE   @@FETCH_STATUS = 0
		BEGIN
			SET	@l_likeStr = '%' + @l_filtervalue + '%'
			IF	@l_columnName = 'clientGroup'
				DELETE	#NoJobClientSubclientSorted WHERE clientGroup NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'client'
				DELETE	#NoJobClientSubclientSorted WHERE client NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'agentType'
				DELETE	#NoJobClientSubclientSorted WHERE agentType NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'instance'
				DELETE	#NoJobClientSubclientSorted WHERE instance NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'backupset'
				DELETE	#NoJobClientSubclientSorted WHERE backupset NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'subclient'
				DELETE	#NoJobClientSubclientSorted WHERE subclient NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'category'
				DELETE	#NoJobClientSubclientSorted WHERE category NOT LIKE @l_likeStr
			ELSE IF	@l_columnName = 'lastProtectedTime'
			BEGIN
				IF ISDATE(@l_filtervalue) = 1
				BEGIN
					SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
					IF @l_filterCondition = '='
						DELETE	#NoJobClientSubclientSorted WHERE lastProtectedTime <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#NoJobClientSubclientSorted WHERE lastProtectedTime <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#NoJobClientSubclientSorted WHERE lastProtectedTime <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#NoJobClientSubclientSorted WHERE lastProtectedTime >= @l_number OR lastProtectedTime <= 0
					ELSE IF @l_filterCondition = '<='
						DELETE	#NoJobClientSubclientSorted WHERE lastProtectedTime >  @l_number OR lastProtectedTime <= 0
				END
			END
			ELSE IF	@l_columnName = 'slaDays'
			BEGIN
				IF @l_filterCondition = 'LIKE'
					DELETE	#NoJobClientSubclientSorted WHERE CAST(slaDays AS VARCHAR(10)) NOT LIKE @l_likeStr
				ELSE IF ISNUMERIC(@l_filtervalue) = 1
				BEGIN
					SET @l_number = CAST(@l_filtervalue AS INT)
					IF @l_filterCondition = '='
						DELETE	#NoJobClientSubclientSorted WHERE slaDays <> @l_number
					ELSE IF @l_filterCondition = '>'
						DELETE	#NoJobClientSubclientSorted WHERE slaDays <= @l_number
					ELSE IF @l_filterCondition = '>='
						DELETE	#NoJobClientSubclientSorted WHERE slaDays <  @l_number
					ELSE IF @l_filterCondition = '<'
						DELETE	#NoJobClientSubclientSorted WHERE slaDays >= @l_number OR slaDays <= 0
					ELSE IF @l_filterCondition = '<='
						DELETE	#NoJobClientSubclientSorted WHERE slaDays >  @l_number OR slaDays <= 0
				END
			END
			FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
		END
		CLOSE	ColumnFilterCur
		DEALLOCATE ColumnFilterCur
		SELECT	@l_totalRows = COUNT(*) FROM #NoJobClientSubclientSorted
		IF	@i_pageSize <= 0
			SET @i_pageSize = @l_totalRows
		ELSE
		IF	@i_pageNo > 1 AND @i_pageSize > 0
			DELETE	TOP (@i_pageSize*(@i_pageNo-1))
			FROM	#NoJobClientSubclientSorted
	END
END
-- Drill down to Excluded Clients: status = 3 (Excluded) and category = 0 (Any)
IF	@i_status = 3
BEGIN
	DELETE	#ExcludedEntity WHERE category = 7
	UPDATE	#ExcludedEntity
	SET		lastProtectedTime = T.lastProtectedTime
	FROM	(SELECT	EC.clientId, MAX(J.servStartDate) AS lastProtectedTime
			FROM	#ExcludedEntity EC
					INNER JOIN APP_Application A WITH (NOLOCK) ON A.clientId = EC.clientId AND A.appTypeId <> 106
					INNER JOIN JMBkpStats J WITH (NOLOCK) ON J.appId = A.id
					INNER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = A.appTypeId
			WHERE	(@i_agentType = '' OR I.displayName = @i_agentType)
				AND J.servStartDate < @toTime
				AND (J.bkpLevel IN (1, 1024, 32768) OR @i_fullJobsOnly = 0 AND J.bkpLevel IN (2, 4, 256))
				AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
				AND (J.displayStatus IN (1, 14) OR J.displayStatus = 3 AND I.isCWEjobValid = 1)
AND	A.subclientStatus&(16|32) = 0 AND A.appTypeId < 600 AND A.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127) AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subclientStatus&8 = 0)
				AND A.id NOT IN (SELECT componentNameId FROM APP_subclientProp WITH (NOLOCK) WHERE attrName = 'DDB Backup' AND attrVal <> '0' AND modified = 0)
				AND A.clientId NOT IN (SELECT componentNameId FROM APP_ClientProp WITH (NOLOCK) WHERE attrName = 'Content Index Server' AND attrVal = '1')
			GROUP BY EC.clientId) T
	WHERE	#ExcludedEntity.clientId = T.clientId
	IF	@i_agentType IN ('', 'Virtual Server')
		UPDATE	#ExcludedEntity
		SET		lastProtectedTime = T.lastProtectedTime
		FROM	(SELECT	EC.clientId, MAX(J.servStartDate) AS lastProtectedTime
				FROM	#ExcludedEntity EC
						INNER JOIN APP_VMProp V WITH (NOLOCK)
							ON V.VMclientId = EC.ClientId AND V.attrName = 'vmStatus' AND V.attrVal IN ('0', '3')
						INNER JOIN JMBkpStats J WITH (NOLOCK)
							ON J.jobId = V.jobId AND J.commCellId = V.commCellId
							AND J.servStartDate < @toTime
							AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
							AND (J.bkpLevel IN (1, 1024, 32768) OR @i_fullJobsOnly = 0 AND J.bkpLevel IN (2, 4, 256))
				GROUP BY EC.clientId) T
		WHERE	#ExcludedEntity.clientId = T.clientId
	IF	@i_sortDirection = 0
		INSERT	INTO #ExcludedEntitySorted
		SELECT	dbo.WR_GetSLACategoryString(@i_localeId, EC.category), CL.displayName,
				ISNULL(CG.name, '')  AS clientGroup,
				ISNULL(I.name, '')   AS agentType,
				ISNULL(dbo.FixInstanceName(INS.name, A.appTypeId), '') AS instance,
				ISNULL(BS.name, '')  AS backupset,
				ISNULL(A.subclientName, '') AS subclient,
				ISNULL(AP.name, '') AS planName,
CASE WHEN EC.installedTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, EC.installedTime, '1970-01-01'), @csTimeZone)) END,
CASE WHEN EC.lastProtectedTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, EC.lastProtectedTime, '1970-01-01'), @csTimeZone)) END,
CASE WHEN EC.lastOnlineTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, EC.lastOnlineTime, '1970-01-01'), @csTimeZone)) END,
CASE WHEN EC.lastOfflineTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, EC.lastOfflineTime, '1970-01-01'), @csTimeZone)) END,
				EC.isOnline,
				EC.slaDays,
				EC.clientId,
				EC.clientType,
				EC.clientGroupId,
				EC.appTypeId,
				EC.instanceId,
				EC.backupsetId,
				EC.appId,
				EC.planId,
				EC.ExcludedReason
		FROM	#ExcludedEntity EC
				INNER JOIN APP_Client CL WITH (NOLOCK) ON CL.id = EC.clientId AND (EC.isVM = 1 OR (CL.specialClientFlags & 1) = 0)
				LEFT OUTER JOIN APP_ClientGroup CG WITH (NOLOCK) ON CG.id = EC.clientGroupId AND CG.id > 0
				LEFT OUTER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = EC.appTypeId AND I.type > 0
				LEFT OUTER JOIN APP_InstanceName INS WITH (NOLOCK) ON INS.id = EC.instanceId AND INS.id > 1
				LEFT OUTER JOIN APP_BackupsetName BS WITH (NOLOCK) ON BS.id = EC.backupsetId AND BS.id > 1
				LEFT OUTER JOIN APP_Application A WITH (NOLOCK) ON A.id = EC.appId AND A.id > 0
				LEFT OUTER JOIN App_plan AP WITH (NOLOCK) ON AP.id = EC.planId AND AP.id > 0
		ORDER BY	(CASE @i_orderBy WHEN  2 THEN CL.displayName
									 WHEN  3 THEN ISNULL(I.name, '')
									 WHEN  4 THEN ISNULL(INS.name, '')
									 WHEN  5 THEN ISNULL(BS.name, '')
									 WHEN  6 THEN ISNULL(A.subclientName, '')
									 WHEN 15 THEN dbo.WR_GetSLACategoryString(@i_localeId, EC.category)
									 WHEN 17 THEN EC.isOnline
									 WHEN 21 THEN EC.clientType
									 WHEN 22 THEN EC.ExcludedReason
									 WHEN 23 THEN ISNULL(AP.name, '')
									 ELSE '' END) ASC,
					(CASE @i_orderBy WHEN  8 THEN EC.lastProtectedTime
									 WHEN 14 THEN EC.lastOnlineTime
									 WHEN 16 THEN EC.lastOfflineTime
									 WHEN 18 THEN EC.installedTime
									 WHEN 19 THEN EC.slaDays
									 ELSE  0 END) ASC,
					(CASE @i_orderBy WHEN 15 THEN '' ELSE dbo.WR_GetSLACategoryString(@i_localeId, EC.category) END) ASC,
					(CASE @i_orderBy WHEN  2 THEN '' ELSE CL.displayName END) ASC
	ELSE
		INSERT	INTO #ExcludedEntitySorted
		SELECT	dbo.WR_GetSLACategoryString(@i_localeId, EC.category), CL.displayName,
				ISNULL(CG.name, '')  AS clientGroup,
				ISNULL(I.name, '')   AS agentType,
				ISNULL(dbo.FixInstanceName(INS.name, A.appTypeId), '') AS instance,
				ISNULL(BS.name, '')  AS backupset,
				ISNULL(A.subclientName, '') AS subclient,
				ISNULL(AP.name, '') AS planName,
CASE WHEN EC.installedTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, EC.installedTime, '1970-01-01'), @csTimeZone)) END,
CASE WHEN EC.lastProtectedTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, EC.lastProtectedTime, '1970-01-01'), @csTimeZone)) END,
CASE WHEN EC.lastOnlineTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, EC.lastOnlineTime, '1970-01-01'), @csTimeZone)) END,
CASE WHEN EC.lastOfflineTime <= 0 THEN -1 ELSE DATEDIFF(SS, '1970-01-01', dbo.UTCToLocalDateTimeConversionManaged(DATEADD(SS, EC.lastOfflineTime, '1970-01-01'), @csTimeZone)) END,
				EC.isOnline,
				EC.slaDays,
				EC.clientId,
				EC.clientType,
				EC.clientGroupId,
				EC.appTypeId,
				EC.instanceId,
				EC.backupsetId,
				EC.appId,
				EC.planId,
				EC.ExcludedReason
		FROM	#ExcludedEntity EC
				INNER JOIN APP_Client CL WITH (NOLOCK) ON CL.id = EC.clientId AND (EC.isVM = 1 OR (CL.specialClientFlags & 1) = 0)
				LEFT OUTER JOIN APP_ClientGroup CG WITH (NOLOCK) ON CG.id = EC.clientGroupId AND CG.id > 0
				LEFT OUTER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = EC.appTypeId AND I.type > 0
				LEFT OUTER JOIN APP_InstanceName INS WITH (NOLOCK) ON INS.id = EC.instanceId AND INS.id > 1
				LEFT OUTER JOIN APP_BackupsetName BS WITH (NOLOCK) ON BS.id = EC.backupsetId AND BS.id > 1
				LEFT OUTER JOIN APP_Application A WITH (NOLOCK) ON A.id = EC.appId AND A.id > 0
				LEFT OUTER JOIN App_plan AP WITH (NOLOCK) ON AP.id = EC.planId AND AP.id > 0
		ORDER BY	(CASE @i_orderBy WHEN  2 THEN CL.displayName
									 WHEN  3 THEN ISNULL(I.name, '')
									 WHEN  4 THEN ISNULL(INS.name, '')
									 WHEN  5 THEN ISNULL(BS.name, '')
									 WHEN  6 THEN ISNULL(A.subclientName, '')
									 WHEN 15 THEN dbo.WR_GetSLACategoryString(@i_localeId, EC.category)
									 WHEN 17 THEN EC.isOnline
									 WHEN 21 THEN EC.clientType
									 WHEN 22 THEN EC.ExcludedReason
									 WHEN 23 THEN ISNULL(AP.name, '')
									 ELSE '' END) DESC,
					(CASE @i_orderBy WHEN  8 THEN EC.lastProtectedTime
									 WHEN 14 THEN EC.lastOnlineTime
									 WHEN 16 THEN EC.lastOfflineTime
									 WHEN 18 THEN EC.installedTime
									 ELSE  0 END) DESC,
					(CASE @i_orderBy WHEN 15 THEN '' ELSE dbo.WR_GetSLACategoryString(@i_localeId, EC.category) END) DESC,
					(CASE @i_orderBy WHEN  2 THEN '' ELSE CL.displayName END) DESC
	OPEN	ColumnFilterCur
	FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
	WHILE   @@FETCH_STATUS = 0
	BEGIN
		SET	@l_likeStr = '%' + @l_filtervalue + '%'
		IF	@l_columnName = 'clientGroup'
			DELETE	#NoJobClientSubclientSorted WHERE clientGroup NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'client'
			DELETE	#NoJobClientSubclientSorted WHERE client NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'agentType'
			DELETE	#NoJobClientSubclientSorted WHERE agentType NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'instance'
			DELETE	#NoJobClientSubclientSorted WHERE instance NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'backupset'
			DELETE	#NoJobClientSubclientSorted WHERE backupset NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'subclient'
			DELETE	#NoJobClientSubclientSorted WHERE subclient NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'clientType'
			DELETE	#ExcludedEntitySorted WHERE clientType NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'category'
			DELETE	#ExcludedEntitySorted WHERE categoryStr NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'excludedReason'
			DELETE	#ExcludedEntitySorted WHERE ExcludedReason NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'plan'
			DELETE	#ExcludedEntitySorted WHERE planName NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'installedTime'
		BEGIN
			IF ISDATE(@l_filtervalue) = 1
			BEGIN
				SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
				IF @l_filterCondition = '='
					DELETE	#ExcludedEntitySorted WHERE installedTime <> @l_number
				ELSE IF @l_filterCondition = '>'
					DELETE	#ExcludedEntitySorted WHERE installedTime <= @l_number
				ELSE IF @l_filterCondition = '>='
					DELETE	#ExcludedEntitySorted WHERE installedTime <  @l_number
				ELSE IF @l_filterCondition = '<'
					DELETE	#ExcludedEntitySorted WHERE installedTime >= @l_number OR installedTime <= 0
				ELSE IF @l_filterCondition = '<='
					DELETE	#ExcludedEntitySorted WHERE installedTime >  @l_number OR installedTime <= 0
			END
		END
		ELSE IF	@l_columnName = 'lastProtectedTime'
		BEGIN
			IF ISDATE(@l_filtervalue) = 1
			BEGIN
				SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
				IF @l_filterCondition = '='
					DELETE	#ExcludedEntitySorted WHERE lastProtectedTime <> @l_number
				ELSE IF @l_filterCondition = '>'
					DELETE	#ExcludedEntitySorted WHERE lastProtectedTime <= @l_number
				ELSE IF @l_filterCondition = '>='
					DELETE	#ExcludedEntitySorted WHERE lastProtectedTime <  @l_number
				ELSE IF @l_filterCondition = '<'
					DELETE	#ExcludedEntitySorted WHERE lastProtectedTime >= @l_number OR lastProtectedTime <= 0
				ELSE IF @l_filterCondition = '<='
					DELETE	#ExcludedEntitySorted WHERE lastProtectedTime >  @l_number OR lastProtectedTime <= 0
			END
		END
		ELSE IF	@l_columnName = 'lastOnlineTime'
		BEGIN
			IF ISDATE(@l_filtervalue) = 1
			BEGIN
				SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
				IF @l_filterCondition = '='
					DELETE	#ExcludedEntitySorted WHERE lastOnlineTime <> @l_number
				ELSE IF @l_filterCondition = '>'
					DELETE	#ExcludedEntitySorted WHERE lastOnlineTime <= @l_number
				ELSE IF @l_filterCondition = '>='
					DELETE	#ExcludedEntitySorted WHERE lastOnlineTime <  @l_number
				ELSE IF @l_filterCondition = '<'
					DELETE	#ExcludedEntitySorted WHERE lastOnlineTime >= @l_number OR lastOnlineTime <= 0
				ELSE IF @l_filterCondition = '<='
					DELETE	#ExcludedEntitySorted WHERE lastOnlineTime >  @l_number OR lastOnlineTime <= 0
			END
		END
		ELSE IF	@l_columnName = 'lastOfflineTime'
		BEGIN
			IF ISDATE(@l_filtervalue) = 1
			BEGIN
				SET @l_number = dbo.GetUnixTimeBig(@l_filtervalue)
				IF @l_filterCondition = '='
					DELETE	#ExcludedEntitySorted WHERE lastOfflineTime <> @l_number
				ELSE IF @l_filterCondition = '>'
					DELETE	#ExcludedEntitySorted WHERE lastOfflineTime <= @l_number
				ELSE IF @l_filterCondition = '>='
					DELETE	#ExcludedEntitySorted WHERE lastOfflineTime <  @l_number
				ELSE IF @l_filterCondition = '<'
					DELETE	#ExcludedEntitySorted WHERE lastOfflineTime >= @l_number OR lastOfflineTime <= 0
				ELSE IF @l_filterCondition = '<='
					DELETE	#ExcludedEntitySorted WHERE lastOfflineTime >  @l_number OR lastOfflineTime <= 0
			END
		END
		ELSE IF	@l_columnName = 'isOnline'
			DELETE	#ExcludedEntitySorted WHERE isOnline NOT LIKE @l_likeStr
		ELSE IF	@l_columnName = 'slaDays'
		BEGIN
			IF @l_filterCondition = 'LIKE'
				DELETE	#ExcludedEntitySorted WHERE CAST(slaDays AS VARCHAR(10)) NOT LIKE @l_likeStr
			ELSE IF ISNUMERIC(@l_filtervalue) = 1
			BEGIN
				SET @l_number = CAST(@l_filtervalue AS INT)
				IF @l_filterCondition = '='
					DELETE	#ExcludedEntitySorted WHERE slaDays <> @l_number
				ELSE IF @l_filterCondition = '>'
					DELETE	#ExcludedEntitySorted WHERE slaDays <= @l_number
				ELSE IF @l_filterCondition = '>='
					DELETE	#ExcludedEntitySorted WHERE slaDays <  @l_number
				ELSE IF @l_filterCondition = '<'
					DELETE	#ExcludedEntitySorted WHERE slaDays >= @l_number OR slaDays <= 0
				ELSE IF @l_filterCondition = '<='
					DELETE	#ExcludedEntitySorted WHERE slaDays >  @l_number OR slaDays <= 0
			END
		END
		FETCH	ColumnFilterCur INTO @l_columnName, @l_filtervalue, @l_filterCondition
	END
	CLOSE	ColumnFilterCur
	DEALLOCATE ColumnFilterCur
	SELECT	@l_totalRows = COUNT(*) FROM #ExcludedEntitySorted
	IF	@i_pageSize <= 0
		SET @i_pageSize = @l_totalRows
	ELSE
	IF	@i_pageNo > 1 AND @i_pageSize > 0
		DELETE	TOP (@i_pageSize*(@i_pageNo-1))
		FROM	#ExcludedEntitySorted
END
IF	@i_pageSize <= 0
	SET @i_pageSize = 0x7FFFFFFF
DROP TABLE #ClientGroupsString
IF (@tableOutput = '')
BEGIN
-- generate XML message
SELECT
	@dateAsOf                           AS '@dateAsOf',
	@l_totalRows                        AS '@totalRows',
	@l_threshold                        AS '@threshold',
    (SELECT @i_days                     AS '@days',
            @i_status                   AS '@status',
            @i_category                 AS '@category',
            @i_orderBy                  AS '@orderBy',
            @i_pageNo                   AS '@pageNo'
     FOR XML PATH('req'), TYPE),
    (SELECT aliasName					AS '@commCellName',
            REPLACE(LTRIM(REPLACE(SUBSTRING(master.sys.fn_varbintohexstr(CONVERT(VARBINARY(8), number)), 3, 8), '0', ' ')), ' ', '0')
										AS '@commCellID',
            ipAddress                   AS '@commServeIP',
            (SELECT name FROM SimMiniBrand WITH (NOLOCK) WHERE MiniBrandId = @l_MiniBrandId)
							            AS '@edition',
            CASE ccOpMode WHEN 1000 THEN 'Evaluation' WHEN 1002 THEN 'Production' WHEN 1004 THEN 'DR Production' ELSE 'Unknown' END
							            AS '@licenseMode',
            timeZone                    AS '@commServeTZ'
	 FROM	APP_CommCell WITH (NOLOCK)
	 WHERE	id = 2
     FOR    XML PATH('ccInfo'),TYPE),
	-- first page: status = 0 (Any) and category = 0 (Any)
	-- Time Frame list
    (SELECT days AS '@val'
     FROM   @tblTimeFrame
     ORDER BY days
     FOR XML PATH('slaDays'), TYPE),
    (SELECT 0							AS '@date',
            ISNULL((SELECT numberOfClients FROM #tblSummary WHERE status = 1), 0)
										AS '@protectedClients',
            ISNULL((SELECT numberOfClients FROM #tblSummary WHERE status = 2), 0)
							            AS '@unprotectedClients',
            ISNULL((SELECT numberOfClients FROM #tblSummary WHERE status = 3), 0)
							            AS '@excludedClients'
     FOR XML PATH('lastSLA'), TYPE),
    (SELECT dbo.GetUnixTime(trendDate)  AS '@date',
            ISNULL(ProtectedClients, 0) AS '@protectedClients',
            ISNULL(MissedClients, 0)    AS '@unprotectedClients',
            ISNULL(ExcludedClients, 0)  AS '@excludedClients'
     FROM   #tblTrend
     ORDER BY trendDate
     FOR XML PATH('trend'), TYPE),
    (SELECT agentType                   AS '@agentType',
            SUM(CASE WHEN status = 1 THEN numberOfClients ELSE 0 END)
                                        AS '@protectedClients',
            SUM(CASE WHEN status = 2 THEN numberOfClients ELSE 0 END)
                                        AS '@unprotectedClients'
     FROM   #tblAgentSummary
     GROUP BY agentType
     FOR XML PATH('agentSLA'), TYPE),
	-- Drill down to status = 1 (Protected) and category = 0 (Any)
    (SELECT TOP (@i_pageSize)
			client                      AS '@client',
            clientGroup                 AS '@clientGroup',
            agentType                   AS '@agentType',
            instance                    AS '@instance',
            backupset                   AS '@backupset',
            subclient                   AS '@subclient',
            lastProtectedTime        	AS '@lastProtectedTime',
            lastBackupType              AS '@lastBackupType',
            slaDays						AS '@slaDays',
            slaDaysLevel				AS '@slaDaysLevel',
			clientId					AS '@clientId',
			appTypeId					AS '@appTypeId',
			instanceId					AS '@instanceId',
			backupsetId					AS '@backupsetId',
			appId						AS '@appId'
     FROM   #ProtectedSubclientSorted
     FOR XML PATH('protectedSubclientList'), TYPE),
	-- Drill down to status = 2 (Unprotected) and category = 0 (Any)
    (SELECT category                    AS '@category',
            counts                      AS '@value'
    FROM	@tblCategoryStats
	ORDER BY category
    FOR XML PATH('unprotectedCategories'), TYPE),
   (SELECT TOP (@i_pageSize)
			client                      AS '@client',
            clientGroup                 AS '@clientGroup',
            categoryName                AS '@category',
			lastProtectedTime        	AS '@lastProtectedTime',
			lastOnlineTime           	AS '@lastSeenTime',
			lastOfflineTime          	AS '@lastOfflineTime',
			isOnline		            AS '@isOnline',
            slaDays						AS '@slaDays',
			clientId					AS '@clientId',
			clientType					AS '@clientType'
    FROM	#MissedClientSorted
    FOR XML PATH('unprotectedClients'), TYPE),
   (SELECT TOP (@i_pageSize)
			client                      AS '@client',
            clientGroup                 AS '@clientGroup',
            agentType                   AS '@agentType',
            instance                    AS '@instance',
            backupset                   AS '@backupset',
			category                    AS '@category',
            lastProtectedTime        	AS '@lastProtectedTime',
            slaDays						AS '@slaDays',
			clientId					AS '@clientId',
			appTypeId					AS '@appTypeId',
			instanceId					AS '@instanceId',
			backupsetId					AS '@backupsetId'
    FROM	#MissedBackupsetSorted
    FOR XML PATH('unprotectedBackupsets'), TYPE),
	-- Drill down to status = 2 (Unprotected) and category = 2 (Failed) or 3 (NoJobRun)
    (SELECT TOP (@i_pageSize)
			client                      AS '@client',
            clientGroup                 AS '@clientGroup',
            agentType                   AS '@agentType',
            instance                    AS '@instance',
            backupset                   AS '@backupset',
            subclient                   AS '@subclient',
			category                    AS '@category',
            lastProtectedTime        	AS '@lastProtectedTime',
            lastBackupType              AS '@lastBackupType',
            lastFailedJobId             AS '@lastFailedJobId',
            lastFailedParentJobId       AS '@lastFailedParentJobId',
            lastFailedTime           	AS '@lastFailedTime',
            lastFailedBackupType        AS '@lastFailedBackupType',
			failureReasons              AS '@failureReasons',
            slaDays						AS '@slaDays',
            slaDaysLevel				AS '@slaDaysLevel',
			clientId					AS '@clientId',
			appTypeId					AS '@appTypeId',
			instanceId					AS '@instanceId',
			backupsetId					AS '@backupsetId',
			appId						AS '@appId'
     FROM   #MissedClientSubclientSorted
     FOR XML PATH('failedSubclientList'), TYPE),
	-- Drill down to status = 2 (Unprotected) and category = 4 (NoSchedule) or 9 (FilteredVM) or 12 (SnapJobsWithNoBackupCopy)
    (SELECT TOP (@i_pageSize)
			client                      AS '@client',
            clientGroup                 AS '@clientGroup',
            agentType                   AS '@agentType',
            instance                    AS '@instance',
            backupset                   AS '@backupset',
            subclient                   AS '@subclient',
			category                    AS '@category',
            lastProtectedTime        	AS '@lastProtectedTime',
            slaDays						AS '@slaDays',
            slaDaysLevel				AS '@slaDaysLevel',
			clientId					AS '@clientId',
			appTypeId					AS '@appTypeId',
			instanceId					AS '@instanceId',
			backupsetId					AS '@backupsetId',
			appId						AS '@appId'
     FROM   #NoJobClientSubclientSorted
     FOR XML PATH('noJobSubclientList'), TYPE),
	-- Drill down to status = 3 (Excluded Client) and category = 0 (Any)
    (SELECT category                    AS '@category',
            COUNT(*)					AS '@value'
    FROM	#ExcludedEntity
	GROUP BY category
	ORDER BY category
    FOR XML PATH('excludedCategories'), TYPE),
	(SELECT	TOP (@i_pageSize)
			categoryStr                 AS '@category',
			client                      AS '@client',
            clientGroup                 AS '@clientGroup',
            agentType                   AS '@agentType',
            instance                    AS '@instance',
            backupset                   AS '@backupset',
            subclient                   AS '@subclient',
			planName						AS '@plan',
			lastProtectedTime        	AS '@lastProtectedTime',
			lastOnlineTime           	AS '@lastSeenTime',
			lastOfflineTime          	AS '@lastOfflineTime',
			isOnline		            AS '@isOnline',
			installedTime	        	AS '@installedTime',
            slaDays						AS '@slaDays',
			clientId					AS '@clientId',
			clientType					AS '@clientType',
			clientGroupId				AS '@clientGroupId',
			appTypeId					AS '@appTypeId',
			instanceId					AS '@instanceId',
			backupsetId					AS '@backupsetId',
			appId						AS '@appId',
			planId						AS '@planId',
			ExcludedReason				AS '@excludedReason'
	FROM	#ExcludedEntitySorted
    FOR XML PATH('excludedClientList'), TYPE)
FOR XML PATH ('WebReport_SLAReportResp')
END
ELSE
BEGIN
		IF object_id('tempdb.dbo.' +@tableoutput) IS NULL
		BEGIN
			PRINT 'Invalid Table Object'
			RETURN
		END
		DECLARE @sqlString NVARCHAR(MAX)
		IF  (@i_status = 0 AND @i_flag = 1)
		BEGIN
			SET @sqlString  = 'INSERT INTO '+ @tableOutput +'
			select ISNULL((SELECT numberOfClients FROM #tblSummary WHERE status = 1), 0)
										,
            ISNULL((SELECT numberOfClients FROM #tblSummary WHERE status = 2), 0)
							            ,
            ISNULL((SELECT numberOfClients FROM #tblSummary WHERE status = 3), 0)
							            '
		END
		IF  (@i_status = 0 AND @i_flag = 2)
		BEGIN
			SET @sqlString  = 'INSERT INTO '+ @tableOutput +'
			SELECT dbo.GetUnixTime(trendDate)  ,
            ISNULL(ProtectedClients, 0) ,
            ISNULL(MissedClients, 0)    ,
            ISNULL(ExcludedClients, 0)
			FROM   #tblTrend
				ORDER BY trendDate'
		END
		IF (@i_status = 3 AND @i_flag = 3)
		BEGIN
			SET @sqlString  = 'INSERT INTO '+ @tableOutput +'
			SELECT
				categoryStr        ,
				client             ,
				clientGroup        ,
				agentType          ,
				instance           ,
				backupset          ,
				subclient          ,
				planName			   ,
				lastProtectedTime  ,
				lastOnlineTime     ,
				lastOfflineTime    ,
				isOnline		   ,
				installedTime	   ,
				slaDays            ,
				clientId           ,
				clientType         ,
				clientGroupId      ,
				appTypeId          ,
				instanceId         ,
				backupsetId        ,
				appId			   ,
				planId			   ,
				ExcludedReason
				FROM    #ExcludedEntitySorted'
		END
		IF (@i_status = 2 AND @i_flag = 4)
		BEGIN
			IF (@i_type = 3)
			BEGIN
				SET @sqlString  = 'INSERT INTO '+ @tableOutput +'
				SELECT
				client ,
				clientGroup,
				agentType,
				instance,
				backupset,
				category,
				CASE WHEN lastProtectedTime = -1 THEN NULL ELSE lastProtectedTime END,
				slaDays,
				clientId,
				appTypeId,
				instanceId,
				backupsetId
				FROM    #MissedBackupsetSorted'
			END
			ELSE
			BEGIN
				SET @sqlString  = 'INSERT INTO '+ @tableOutput +'
				SELECT
				client ,
				clientGroup,
				categoryName,
				CASE WHEN lastProtectedTime = -1 THEN NULL ELSE lastProtectedTime END ,
				CASE WHEN lastOnlineTime = -1 THEN NULL ELSE lastOnlineTime END ,
				CASE WHEN lastOfflineTime = -1 THEN NULL ELSE lastOfflineTime END ,
				isOnline,
				slaDays,
				clientId,
				clientType
				FROM    #MissedClientSorted'
			END
		END
		IF (@i_status = 2 AND @i_flag = 5)
		BEGIN
			SET @sqlString  = 'INSERT INTO '+ @tableOutput +'
			SELECT
            client,
            clientGroup,
            agentType,
            instance,
            backupset,
            subclient,
            category,
            lastProtectedTime,
            lastBackupType,
            lastFailedJobId,
            lastFailedParentJobId,
            lastFailedTime,
            lastFailedBackupType,
            failureReasons,
            slaDays,
			slaDaysLevel,
            clientId,
            appTypeId,
            instanceId,
            backupsetId,
            appId
		FROM   #MissedClientSubclientSorted'
		END
		IF (@i_status = 2 AND @i_flag = 6)
		BEGIN
			SET @sqlString  = 'INSERT INTO '+ @tableOutput +'
			SELECT
            client,
            clientGroup,
            agentType,
            instance,
            backupset,
            subclient,
            category,
            lastProtectedTime,
			slaDays,
			slaDaysLevel,
            clientId,
            appTypeId,
            instanceId,
            backupsetId,
            appId
			 FROM   #NoJobClientSubclientSorted'
		END
		IF (@i_status = 1  AND @i_flag = 7)
		BEGIN
			SET @sqlString  = 'INSERT INTO '+ @tableOutput +'
			select
			client,
            clientGroup,
            agentType,
            instance,
            backupset,
            subclient,
            lastProtectedTime,
            lastBackupType,
            slaDays,
			slaDaysLevel,
            clientId,
            appTypeId,
            instanceId,
            backupsetId,
            appId
     FROM   #ProtectedSubclientSorted'
		END
		IF (@i_status = 0  AND @i_flag = 8)
		BEGIN
			SET @sqlString  = 'INSERT INTO '+ @tableOutput +'
			SELECT agentType,
            SUM(CASE WHEN status = 1 THEN numberOfClients ELSE 0 END)
                        ,
            SUM(CASE WHEN status = 2 THEN numberOfClients ELSE 0 END)
		     FROM   #tblAgentSummary
				GROUP BY agentType'
		END
		EXEC (@sqlString)
END
DROP TABLE #LastBackupJobs
DROP TABLE #SubclientSLA
DROP TABLE #ClientFilter
DROP TABLE #ClientAggr
DROP TABLE #ProtectedSubclientSorted
DROP TABLE #MissedClientSorted
DROP TABLE #MissedClientSubclientSorted
DROP TABLE #NoJobClientSubclientSorted
DROP TABLE #ExcludedEntity
DROP TABLE #ExcludedEntitySorted
DROP TABLE #tblSummary
DROP TABLE #tblTrend
DROP TABLE #tblAgentSummary
GO

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

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

insert into GXDBVersions values(2, 'WR_SLA',  'v1.21.2.77.6.5', 'WR_SLA', 'v1.21.2.77.6.5')
GO

