

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/AppProcessEntitiesForAutoDeletion.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.
-- ----------------------------------------------------------------------*/
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='AppProcessEntitiesForAutoDeletion')
	delete from GXDBVersions where aliasname = 'AppProcessEntitiesForAutoDeletion'
GO
print '... Creating Procedure: AppProcessEntitiesForAutoDeletion'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure AppProcessEntitiesForAutoDeletion
AS
  DECLARE @errorCode INT
  DECLARE @errorString NVARCHAR(MAX)
  DECLARE @outXml XML
SET NOCOUNT ON
BEGIN TRY
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	SET @errorCode = 0
	SET @errorString = ''
	DECLARE @nowTime BIGINT = datediff(second, '01/01/1970', GetUTCdate())
	-----------------------GET CLIENTS FOR AUTO RETIRE/DELETION BASED ON COMMCELL/COMPANY/PLAN MANAGE OFFLINE DEVICES SETTING-----------------------
	IF object_id('tempdb.dbo.#OfflineClientsList') IS NOT NULL
	DROP TABLE #OfflineClientsList
	CREATE TABLE #OfflineClientsList
(clientId INT PRIMARY KEY, planId INT DEFAULT 0, companyId INT DEFAULT 0, numOfDaysToRetire INT DEFAULT 183, numOfDaysToDelete INT DEFAULT 365,
	lastOfflineTime BIGINT DEFAULT 0, retire INT DEFAULT 0, eligibleForDeletion INT DEFAULT 0, deconfigured INT DEFAULT 0)
	-- FIRST CHECK IF RETIRE OFFLINE CLIENTS FEATURE IS ON OR NOT TO DETERMINE IF WE NEED TO DO THE FOLLOWING PROCESS
	DECLARE @isRetireOfflineClientFeatureEnabled INT = ISNULL(
		(SELECT (CASE WHEN CAST(value AS VARCHAR(64)) = 'false' THEN 0 ELSE 1 END)
		 FROM APP_AdvanceSettings WHERE keyName = 'showRetireOfflineDevices' AND relativePath = 'WebConsole' AND enabled = 1 AND deleted = 0), 1)
	IF @isRetireOfflineClientFeatureEnabled = 1
	BEGIN
DECLARE @numOfDaysToRetireCommCell INT = ISNULL((SELECT CAST(value AS INT) FROM GxGlobalParam WHERE name = 'Retire devices after days' AND modified = 0), 183)
DECLARE @numOfDaysToDeleteCommCell INT = ISNULL((SELECT CAST(value AS INT) FROM GxGlobalParam WHERE name = 'Force delete devices after days' AND modified = 0), 365)
		-- INSERT ALL OFFLINE LAPTOP CLIENTS TO TABLE #OfflineClientsList
		INSERT INTO #OfflineClientsList
		(clientId, lastOfflineTime, deconfigured)
SELECT C.id, CCRCTC.lastOfflineTime, (CASE WHEN (C.status & 0x00002 = 0x00002 AND C.specialClientFlags & 0x2 = 0x2) THEN 1 ELSE 0 END)
		FROM App_Client C
		JOIN CCRClientToClient CCRCTC
ON C.status & 0x1000 = 0x1000
			AND CCRCTC.fromClientId = 2 AND CCRCTC.lastOnlineTime < CCRCTC.lastOfflineTime 	-- fromClientId FILTER ADDED HERE IS TO MAKE SURE THAT THE RECORDS IS THE CONNECTION FROM COMMCELL TO CLIENT
			AND CCRCTC.toClientId = C.id
		-- FOR LAPTOP CLIENTS THAT HAVE BEEN DECONFIGURED, WE NEED TO FIND THE LAST OFFLINE TIME FROM APP_ClientProp
		INSERT INTO #OfflineClientsList
		(clientId, lastOfflineTime, deconfigured)
		SELECT C.id, CAST(CP.attrVal AS INT), 1
		FROM App_Client C
		JOIN App_ClientProp CP
ON C.status & 0x1000 = 0x1000
AND CP.attrName = 'Last offline time'	AND CP.modified = 0 AND CP.componentNameId = C.id
		LEFT JOIN CCRClientToClient CCR ON CCR.fromClientId = 2 AND C.id = CCR.toClientId
		WHERE CCR.toClientId IS NULL
		-- UPDATE COMPANY ID AND PLAN ID ASSOCIATED WITH CLIENTS IN #OfflineClientsList
		UPDATE #OfflineClientsList
		SET planId = ISNULL(CAST(P2.attrVal AS INT), 0), companyId = ISNULL(CAST(P1.attrVal AS INT), 0)
		FROM #OfflineClientsList C
LEFT JOIN App_ClientProp P1 ON P1.attrName = 'Installation Company ID' AND P1.modified = 0 AND C.clientId = P1.componentNameId
LEFT JOIN App_ClientProp P2 ON P2.attrName = 'Associated Plan' AND P1.modified = 0 AND C.clientId = P2.componentNameId
		-- GET TRUE COMPANY ID FOR COMPANY ID IS 0
		UPDATE #OfflineClientsList
		SET companyId = C1.companyId
		FROM #OfflineClientsList C
		JOIN
		(
			SELECT companyId, clientId, rownumber FROM
			(
				SELECT ACP.componentNameId companyId, RCL.clientId clientId, ROW_NUMBER() OVER (PARTITION BY RCL.clientId ORDER BY ACP.componentNameId) AS rownumber
				FROM #OfflineClientsList RCL
				INNER JOIN APP_ClientGroupAssoc ACGA ON RCL.companyId = 0 AND RCL.clientId = ACGA.clientId
				INNER JOIN App_CompanyProp ACP ON ACP.attrVal = CAST(ACGA.clientGroupId AS NVARCHAR(12))
AND ACP.attrName='Associated Smart Client Group' AND cs_attrName = checksum('Associated Smart Client Group') AND ACP.modified = 0
			) C2 WHERE rownumber = 1
		) C1 ON C.clientId = C1.clientId
		UPDATE #OfflineClientsList
		SET numOfDaysToRetire = -1, numOfDaysToDelete = -1
		where planId <> 0
		-- UPDATE numOfDaysToRetire WITH PLAN LEVEL SETTING
		UPDATE #OfflineClientsList
		SET numOfDaysToRetire = CAST(PP1.attrVal AS INT)
		FROM #OfflineClientsList C
JOIN App_PlanProp PP1 ON C.planId <> 0 AND PP1.attrName = 'Retire devices after days' AND PP1.modified = 0 AND C.planId = PP1.componentNameId
		UPDATE #OfflineClientsList
		SET numOfDaysToDelete = CAST(PP1.attrVal AS INT)
		FROM #OfflineClientsList C
JOIN App_PlanProp PP1 ON C.planId <> 0 AND PP1.attrName = 'Force delete devices after days' AND PP1.modified = 0 AND C.planId = PP1.componentNameId
		-- UPDATE numOfDaysToRetire WITH COMPANY LEVEL SETTING, IF IT IS NOT SET AT PLAN LEVEL
		UPDATE #OfflineClientsList
		SET numOfDaysToRetire = CAST(CP1.attrVal AS INT)
		FROM #OfflineClientsList C
JOIN App_CompanyProp CP1 ON (C.planId = 0 OR (C.planId <> 0 AND C.numOfDaysToRetire = -1)) AND C.companyId > 0 AND CP1.attrName = 'Retire devices after days' AND CP1.cs_attrName = CHECKSUM('Retire devices after days') AND CP1.modified = 0 AND C.companyId = CP1.componentNameId
		UPDATE #OfflineClientsList
		SET numOfDaysToDelete = CAST(CP1.attrVal AS INT)
		FROM #OfflineClientsList C
JOIN App_CompanyProp CP1 ON (C.planId = 0 OR (C.planId <> 0 AND C.numOfDaysToDelete = -1 )) AND C.companyId > 0 AND CP1.attrName = 'Force delete devices after days' AND CP1.cs_attrName = CHECKSUM('Force delete devices after days') AND CP1.modified = 0 AND C.companyId = CP1.componentNameId
		-- UPDATE numOfDaysToRetire, numOfDaysToDelete WITH COMMCELL LEVEL SETTING, IF NEITHER PLAN LEVEL AND COMPANY LEVEL HAS IT SET AND COMMCELL HAS IT SET
		UPDATE #OfflineClientsList
		SET numOfDaysToRetire = @numOfDaysToRetireCommCell
		WHERE companyId = 0 AND (planId = 0  OR (planId <> 0 AND numOfDaysToRetire = -1))
		UPDATE #OfflineClientsList
		SET numOfDaysToDelete = @numOfDaysToDeleteCommCell
		WHERE companyId = 0 AND (planId = 0  OR (planId <> 0 AND numOfDaysToDelete = -1))
		-- SET OFFLINE CLIENTS THAT NEED TO BE RETIRED
		UPDATE #OfflineClientsList
		SET retire = 1
		WHERE numOfDaysToRetire > 0 AND (@nowTime - lastOfflineTime) > (numOfDaysToRetire * 86400)
		-- SET OFFLINE CLIENTS THAT NEED TO BE DELETED
		UPDATE #OfflineClientsList
		SET eligibleForDeletion = 1
		WHERE numOfDaysToDelete > 0 AND (@nowTime - lastOfflineTime) > (numOfDaysToDelete * 86400)
		-- UPDATE CLIENT PROPERTY 'Number of days to keep data of de-configured clients'
		MERGE App_ClientProp AS TARGET
USING (SELECT clientId, CAST(numOfDaysToDelete AS NVARCHAR(1024)) FROM #OfflineClientsList WHERE retire = 1) AS SOURCE (clientId, numOfDaysToDelete)
ON TARGET.attrName = 'Number of days to keep data of de-configured clients' AND TARGET.modified = 0 AND TARGET.componentNameId = SOURCE.clientId
		WHEN MATCHED AND TARGET.attrVal <> SOURCE.numOfDaysToDelete THEN
			UPDATE SET TARGET.attrVal = SOURCE.numOfDaysToDelete
		WHEN NOT MATCHED THEN
			INSERT (componentNameId, attrName, attrType, attrVal, created, modified)
VALUES (SOURCE.clientId, 'Number of days to keep data of de-configured clients', 7, SOURCE.numOfDaysToDelete, @nowTime, 0);
	END
	-----------------------GET BACKUPSETS/AGENTS/CLIENTS FOR AUTO DELETION BASED ON STATUS OF BACKUP SETS-----------------------
	IF object_id('tempdb.dbo.#BackupSetsToBeDeleted') IS NOT NULL
	DROP TABLE #BackupSetsToBeDeleted
	CREATE TABLE #BackupSetsToBeDeleted
	(clientId INT, appTypeId INT, backupsetId INT PRIMARY KEY, subclientId INT)
	IF object_id('tempdb.dbo.#AgentsToBeDeleted') IS NOT NULL
	DROP TABLE #AgentsToBeDeleted
	CREATE TABLE #AgentsToBeDeleted
	(clientId INT, appTypeId INT, PRIMARY KEY(clientId, appTypeId) )
	IF object_id('tempdb.dbo.#ClientsToBeDeleted') IS NOT NULL
	DROP TABLE #ClientsToBeDeleted
	CREATE TABLE #ClientsToBeDeleted
	(clientId INT PRIMARY KEY)
	CREATE TABLE #VMEntitiesToBeDeleted
	(clientId INT, appTypeId INT, vsaInstanceId INT DEFAULT 0, backupsetId INT)
	-- INSERT VM CLIENTS' BACKUP SETS THAT ARE MARKED AS DELETED INTO #BackupSetsToBeDeleted
	INSERT INTO #BackupSetsToBeDeleted
	(clientId, appTypeId, backupsetId, subclientId)
	SELECT APP.clientId, APP.appTypeId, APP.backupSet, APP.id
	FROM APP_BackupSetName BKS
JOIN APP_Application APP ON BKS.status & 0x00004 = 0x00004 AND BKS.id = APP.backupSet
JOIN APP_ClientProp CP ON CP.attrName = 'Virtual Server Discovered Clients' AND CP.attrVal = '1' AND CP.modified = 0 AND APP.clientId = CP.componentNameId
	-- INSERT AGENTS THAT DO NOT HAVE ANY UNDELETED BACKUP SETS INTO #AgentsToBeDeleted, PLUS CLIENTS OF THE AGENTS BELONG TO CANNOT HAVE VSA PACKAGE INSTALLED
	INSERT INTO #AgentsToBeDeleted
	(clientId, appTypeId)
	SELECT DISTINCT DELETEDBKS.clientId, DELETEDBKS.appTypeId
	FROM #BackupSetsToBeDeleted DELETEDBKS
	LEFT JOIN
	(
		SELECT DISTINCT BKSD.clientId clientId, BKSD.appTypeId appTypeId
		FROM (SELECT DISTINCT clientId, appTypeId FROM #BackupSetsToBeDeleted) BKSD
		JOIN App_Application APP ON BKSD.clientId = APP.clientId AND BKSD.appTypeId = APP.appTypeId
JOIN APP_BackupSetName BKS ON BKS.status & 0x00004 = 0 AND APP.backupSet = BKS.id
	) ACTIVEBKS ON DELETEDBKS.clientId = ACTIVEBKS.clientId AND DELETEDBKS.appTypeId = ACTIVEBKS.appTypeId
	WHERE ACTIVEBKS.clientId IS NULL
	AND DELETEDBKS.clientId NOT IN
	(
		SELECT DISTINCT clientId FROM simInstalledPackages
WHERE simPackageId IN (1136 , 713)
	)
	-- INSERT CLIENTS THAT HAVE ONLY ONE AGENT WHICH NEEDS TO BE DELETED INTO #ClientsToBeDeleted, PLUS CLIENTS CANNOT HAVE ANY VSA AND MA PACKAGES INSTALLED
	INSERT INTO #ClientsToBeDeleted
	(clientId)
	SELECT T.clientId
	FROM
	(
		SELECT COUNT(DISTINCT IDA.appTypeId) numOfIDAs, clientId
		FROM APP_IDAName IDA
		WHERE IDA.clientId IN
		(
			SELECT DISTINCT clientId
			FROM #AgentsToBeDeleted
			WHERE clientId NOT IN (SELECT DISTINCT clientId FROM simInstalledPackages)
		)
		GROUP BY IDA.clientId
	) T
	WHERE T.numOfIDAs = 1
	-- REMOVE AGENTS FROM #AgentsToBeDeleted IF THE CLIENTS WILL GET DELETED
	DELETE FROM #AgentsToBeDeleted
	WHERE clientId IN (SELECT clientId FROM #ClientsToBeDeleted)
	-- REMOVE BACKUPSETS FROM #BackupSetsToBeDeleted IF THE CLIENTS WILL GET DELETED
	DELETE #BackupSetsToBeDeleted
	WHERE clientId IN (SELECT clientId FROM #ClientsToBeDeleted)
	-- REMOVE BACKUPSETS FROM #BackupSetsToBeDeleted IF THE AGENTS WILL GET DELETED
	DELETE #BackupSetsToBeDeleted
	FROM #BackupSetsToBeDeleted BKS
	LEFT JOIN #AgentsToBeDeleted AGENTS
	ON BKS.clientId = AGENTS.clientId AND BKS.appTypeId = AGENTS.appTypeId
	WHERE AGENTS.clientId IS NOT NULL
	INSERT INTO #VMEntitiesToBeDeleted
	(clientId, appTypeId, backupsetId)
	(
		SELECT clientId, NULL, NULL FROM #ClientsToBeDeleted
	)
	UNION
	(
		SELECT clientId, appTypeId, NULL FROM #AgentsToBeDeleted
	)
	UNION
	(
		SELECT clientId, NULL, backupsetId FROM #BackupSetsToBeDeleted
	)
	-- UPDATE COMPANY ID IN #VMEntitiesToBeDeleted
	UPDATE #VMEntitiesToBeDeleted
	SET vsaInstanceId = CAST(CP.attrVal AS INT)
	FROM #VMEntitiesToBeDeleted E
	JOIN App_ClientProp CP ON CP.attrName = 'Virtual Machine Instance ID' AND modified = 0 AND CP.componentNameId = E.clientId
	DECLARE @offlineClientsToBeRetiredXmlStr NVARCHAR(MAX), @offlineClientsToBeDeletedXmlStr NVARCHAR(MAX), @clientsToBeDeletedXmlStr NVARCHAR(MAX), @agentsToBeDeletedXmlStr NVARCHAR(MAX), @backupsetsToBeDeletedXmlStr NVARCHAR(MAX)
	DECLARE @vmEntitiesToBeDeletedXmlStr NVARCHAR(MAX)
	SET @offlineClientsToBeRetiredXmlStr = (
SELECT clientId AS 'client/@clientId', 3 AS 'client/@_type_', companyId AS 'ownerCompany/@providerId'
		FROM #OfflineClientsList
		WHERE deconfigured = 0 AND retire = 1
		FOR XML PATH ('offlineClient')
	)
	SET @offlineClientsToBeDeletedXmlStr = (
SELECT clientId AS 'client/@clientId', 3 AS 'client/@_type_', companyId AS 'ownerCompany/@providerId'
		FROM #OfflineClientsList
		WHERE deconfigured = 1 AND eligibleForDeletion = 1
		FOR XML PATH ('deconfiguredOfflineClient')
	)
	SET @vmEntitiesToBeDeletedXmlStr = (
		SELECT (CASE WHEN backupsetId IS NULL THEN clientId ELSE NULL END) AS 'entity/@clientId', appTypeId AS 'entity/@applicationId', backupsetId AS 'entity/@backupsetId',
(CASE WHEN backupsetId IS NOT NULL THEN 6 WHEN appTypeId IS NOT NULL THEN 4 ELSE 3 END) AS 'entity/@_type_',
		vsaInstanceId AS 'vsaInstance/@instanceId'
		FROM #VMEntitiesToBeDeleted
		FOR XML PATH('vmEntity')
	)
END TRY
BEGIN CATCH
PRINT  'INSIDE CATCH BLOCK WITH FOLLOWING ERROR:
	ERROR CODE: ' + CAST(ERROR_NUMBER() AS VARCHAR) + '
	PROC NAME: ' + ISNULL(ERROR_PROCEDURE(), '???') + '
	ERROR LINE NO: ' + CAST(ERROR_LINE() AS VARCHAR)  + '
	ERROR MESSAGE: ' + ERROR_MESSAGE() + '
	ERROR SEVERITY: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +  '
	ERROR STATE: ' + CAST(ERROR_STATE() AS VARCHAR)
    set @errorCode = ERROR_NUMBER()
    set @errorString = 'Procedure [' + ERROR_PROCEDURE() + '] Error Line [' +Convert(varchar(5), ERROR_LINE()) +']. ' +ERROR_MESSAGE()
END CATCH
	DECLARE @errorXmlStr NVARCHAR(MAX) = (
		SELECT @errorCode AS '@errorCode', @errorString AS '@errorMessage' FOR XML PATH ('error')
	)
	DECLARE  @outputstr NVARCHAR(MAX) = ISNULL(@offlineClientsToBeRetiredXmlStr, '') +  ISNULL(@offlineClientsToBeDeletedXmlStr, '') + ISNULL(@vmEntitiesToBeDeletedXmlStr, '') + ISNULL(@errorXmlStr, '')
	SET @outXml = @outputstr
	SET @outXml = (SELECT @outXml FOR XML PATH(''), ROOT('App_ProcessEntitiesForAutoDeletionResponse'))
IF object_id('tempdb.dbo.#OfflineClientsList') IS NOT NULL
	DROP TABLE #OfflineClientsList
IF object_id('tempdb.dbo.#BackupSetsToBeDeleted') IS NOT NULL
	DROP TABLE #BackupSetsToBeDeleted
IF object_id('tempdb.dbo.#AgentsToBeDeleted') IS NOT NULL
	DROP TABLE #AgentsToBeDeleted
IF object_id('tempdb.dbo.#ClientsToBeDeleted') IS NOT NULL
	DROP TABLE #ClientsToBeDeleted
IF object_id('tempdb.dbo.#VMEntitiesToBeDeleted') IS NOT NULL
	DROP TABLE #VMEntitiesToBeDeleted
SELECT @errorCode AS errorCode, @errorString AS errorString, @outXml as outXml
SET NOCOUNT OFF
GO

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

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

insert into GXDBVersions values(2, 'AppProcessEntitiesForAutoDeletion',  '00000000000000000000', 'AppProcessEntitiesForAutoDeletion', '00000000000000000000')
GO

