

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/AppAssocSubClientPolicy.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.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/AppAssocSubClientPolicy.sp,v $ $Id: AppAssocSubClientPolicy.sp,v 1.5.88.11.20.1 2021/07/07 02:57:05 pwang Exp $";
-- 	+-----------------------------------------------------------------------+
--	| 					Procedure: "AppAssocSubClientPolicy"				|
--	| 					Previously Cursor: "AppAssocSCP"					|
--	|																		|
--  |Associate the Subclient Policy and underneath subclients to backupset	|
--	|  Converted from Cursor of same name 13-Oct-2011  -StuKatz				|
--  |backupset to be associated can be either provided via i_id or through  |
--  | temptable #backupsetToAssociate										|
-- 	+-----------------------------------------------------------------------+
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='AppAssocSubClientPolicy')
	delete from GXDBVersions where aliasname = 'AppAssocSubClientPolicy'
GO
print '... Creating Procedure: AppAssocSubClientPolicy'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure AppAssocSubClientPolicy
  @i_id integer,
  @scpId integer,
  @timeStamp integer
AS
  DECLARE @rc integer
   set nocount ON
   declare @appId			integer
   declare @appClientId		integer
   declare @appAppTypeId	integer
   declare @appinstanceId	integer
   declare @appbackupsetId	integer
   declare @appappNumberId	integer
   declare @appdArchId		integer
   declare @applArchId		integer
   declare @apprefTime		integer
   declare @appmodifiedTime	integer
   declare @appscStatus		integer
   declare @compress		integer
   declare @nwAgents		integer
   declare @NWWriteSpeed	integer
   declare @appscName		nvarchar(128)
   declare @useSubclientPolicyRetention integer
   BEGIN
IF OBJECT_ID('tempdb.dbo.#DecoupledRetentionProperties') IS NOT null
DROP TABLE #DecoupledRetentionProperties
CREATE TABLE #DecoupledRetentionProperties
(
	attrName nvarchar(510)
)
INSERT #DecoupledRetentionProperties(attrName)
VALUES(N'Archived expiration days'),
		(N'Is Turbo Subclient'),
		(N'Is Archive Retention Mode Enabled'),
		(N'Is Backup Retention Mode Enabled'),
		(N'Days to keep items past their expiration'),
		(N'Keep older Synth Full Versions'),
		(N'Keep items past their expiration'),
		(N'Number of Days of Synth Full Versions'),
		(N'Number of Synth Full Versions'),
		(N'User deleted stubs expiration days'),
		(N'Retain daily version period'),
		(N'Retain weekly version period'),
		(N'Retain monthly version period')
	   IF object_id('tempdb.dbo.#bkpSetInfo') IS NOT NULL
			DROP TABLE #bkpSetInfo
	   CREATE TABLE #bkpSetInfo(backupsetId INT, newClientId INT, newAppTypeId INT,newInstanceId INT,newSCId INT,clientReleaseId INT,dataArchGrpID INT,isSCInserted  INT)
	   IF(@i_id<>0)
	   BEGIN
			INSERT INTO #bkpSetInfo(backupsetId) VALUES(@i_id)
	   END
	   ELSE
			INSERT INTO #bkpSetInfo(backupsetId) SELECT backupsetId FROM #AssociateBackupsets
		--Set Enable Archive on agents of associated backupset
		DECLARE @ESPEnabled INT = ISNULL((SELECT TOP 1 1 FROM APP_Application subclient
											INNER JOIN APP_SubClientProp prop
												ON subclient.id = prop.componentNameId
											INNER JOIN APP_SubClientProp ESP
												ON subclient.id = ESP.componentNameId
										WHERE subclient.backupSet = @scpId
											AND prop.attrName = N'Subclient Policy Has Retention' AND prop.attrVal = '1' AND prop.modified = 0
											AND ESP.attrName = N'Is Turbo Subclient' AND ESP.attrVal = N'1' AND ESP.modified = 0)
									, 0)
		--Subclient policy has Retention tab with Extend storage policy property. If this property is enabled then set Enable Archive on FS agent of associated backupset.
		IF @ESPEnabled = 1
		BEGIN
			IF OBJECT_ID('tempdb.dbo.#ESPAgents') IS NOT NULL
			DROP TABLE #ESPAgents
			CREATE TABLE #ESPAgents (agentId INT PRIMARY KEY )
			INSERT #ESPAgents
			SELECT DISTINCT agent.id
			FROM APP_Application subclient
			INNER JOIN APP_IDAName agent ON agent.clientId = subclient.clientId AND agent.appTypeId = subclient.appTypeId
			INNER JOIN APP_AppTypeGroupAssoc appType ON appType.appTypeId = subclient.appTypeId AND appType.appGroupId IN(22,34) AND appType.typeOfGroup = 0 --22:WinFS and 34:UnixFS
			INNER JOIN #bkpSetInfo associateBkpSet ON associateBkpSet.backupsetId = subclient.backupset
			INNER JOIN APP_ClientProp client ON client.componentNameId = subclient.clientId
			WHERE client.attrName = N'IndexingV2' AND client.attrVal = N'1' AND client.modified = 0
			UPDATE T
			SET T.modified = @timeStamp
			FROM APP_IDAProp T
			INNER JOIN #ESPAgents as Src ON T.componentNameId = Src.agentId
			WHERE T.attrName = N'Enable Archive' AND T.attrVal = N'0' AND T.modified = 0
			MERGE APP_IDAProp as Dest
			USING #ESPAgents as Src
			ON Dest.componentNameId = Src.agentId AND Dest.attrName = N'Enable Archive' AND Dest.attrVal = N'1' AND Dest.modified = 0
			WHEN NOT MATCHED THEN
				INSERT (componentNameId, attrName, attrType, attrVal, created, modified, ccpId)
				VALUES(Src.agentId, N'Enable Archive', 2, N'1', @timeStamp, 0, 0);
		END
		-- SET THE INITIAL VALUES
		UPDATE BkpSet SET BkpSet.newClientId = clientId, BkpSet.newAppTypeId = appTypeId , BkpSet.newInstanceId = instance
		FROM #bkpSetInfo BkpSet
		INNER JOIN App_Application App WITH(NOLOCK) ON App.backupset = BkpSet.backupsetId
		UPDATE BkpSet SET BkpSet.clientReleaseId = ISNULL(releaseId,0)
		FROM #bkpSetInfo BkpSet
		INNER JOIN App_Client WITH (nolock) ON id = newClientId
		DECLARE	GetSubclientsCursor CURSOR FOR
		SELECT id,clientId,appTypeId,instance,backupSet,appNumber,dataArchGrpID,logArchGrpID,refTime,modified,subclientName,subclientStatus
			FROM APP_Application where backupset = @scpId
		OPEN GetSubclientsCursor
		FETCH GetSubclientsCursor INTO @appId, @appClientId, @appAppTypeId ,@appinstanceId, @appbackupsetId, @appappNumberId, @appdArchId,
									@applArchId, @apprefTime,   @appmodifiedTime, @appscName, @appscStatus
		WHILE	@@FETCH_STATUS = 0
		BEGIN
			UPDATE #bkpSetInfo SET newSCId = 0, isSCInserted = 0, dataArchGrpID=0
			--if it is default subclient, update will take place.
			IF ((@appscStatus & 8) = 8)
			BEGIN
			  UPDATE BkpSet SET BkpSet.newSCId = App.id, BkpSet.dataArchGrpID = App.dataArchGrpID
			  FROM #bkpSetInfo BkpSet
			  INNER JOIN APP_Application App WITH(NOLOCK) ON App.backupset = BkpSet.backupsetId AND ((App.subclientStatus & 8) = 8)
			  --MR 146895: Do not change last backuptime
			  --If there is change in the dataarchiveID or logarchiveId, then we need to change the last backup time and set the reason for clearinig the backup time
			  --MR: 163985: Now bring back the code, it is enabled on key bEnablePopUpMessageDuringStoragePolicyChange
				IF EXISTS(SELECT 1 FROM APP_AdvanceSettings WHERE keyName = 'bEnablePopUpMessageDuringStoragePolicyChange' AND relativePath = 'CommServDB.Console' AND type = 'BOOLEAN' AND cast([value] as nvarchar(32)) = N'true' AND enabled = 1 AND deleted = 0 AND entityType = 3 AND entityId = 2)
				BEGIN
					UPDATE scp SET attrVal = N'0'
					FROM APP_SubClientProp scp
					INNER JOIN #bkpSetInfo BkpSet ON BkpSet.newSCId = scp.componentNameId AND scp.attrName IN('sys:last full','sys:last incr')  AND scp.modified=0
					WHERE BkpSet.dataArchGrpID <> @appdArchId
					MERGE APP_SubClientProp as Target
					USING
					(
						SELECT bkpset.newSCid AS componentNameId, N'Reason last backup time cleared' as attrName
						FROM #bkpSetInfo bkpset
						WHERE bkpset.dataArchGrpID <> @appdArchId
					)AS SOURCE
					ON(Target.componentNameId = Source.componentNameId AND Target.attrName = Source.attrName AND Target.modified=0)
					WHEN MATCHED THEN
						UPDATE SET Target.attrval = N'9'
					WHEN NOT MATCHED BY TARGET THEN
						INSERT(componentNameId,attrName,attrType,attrVal,created,modified)
						VALUES(Source.componentNameId,Source.attrName,7,9,@timeStamp,0);
				END
UPDATE App  set App.dataArchGrpID = @appdArchId,  App.logArchGrpID = @applArchId, App.subclientName = @appscName , App.subclientStatus = (App.subclientStatus & 0x0100) | @appscStatus
			   FROM  APP_Application App
			   INNER JOIN #bkpsetinfo bkpset ON App.id = bkpset.newSCId
			END
			--if it is iBMi Subclient then update update precreated subclients
			ELSE IF((@appscStatus & 0x400000) = 0x400000)
			BEGIN
				UPDATE BkpSet SET BkpSet.newSCId = App.id FROM #bkpSetInfo BkpSet  INNER JOIN APP_Application App WITH(NOLOCK) ON App.backupSet = BkpSet.backupsetId and App.subclientName =  @appscName where (App.subclientStatus & 0x400000) = 0x400000
UPDATE App SET App.dataArchGrpID = @appdArchId,  App.logArchGrpID = @applArchId, App.subclientName = @appscName , App.subclientStatus = (App.subclientStatus & 0x0100) | @appscStatus
				from APP_Application App INNER JOIN #bkpSetInfo bkpSet ON App.id = bkpSet.newSCId
			END
			--if it is system state subclient, update will take place.
 			ELSE IF (EXISTS (select * from APP_SubClientProp where componentnameId = @appId and modified = 0 and attrName = 'System State Subclient'))
 			BEGIN
			  UPDATE bkpset SET newSCId = App.id , BkpSet.dataArchGrpID = App.dataArchGrpID
			  FROM #bkpsetInfo bkpset
			  INNER JOIN App_Application App ON App.backupset = bkpset.backupsetId
			  INNER JOIN App_SubClientProp SCP ON SCP.componentNameId = App.id AND SCP.modified = 0 AND SCP.attrName = 'System State Subclient'
 			  --MR 146895: Do not change last backuptime
			  --If there is change in the dataarchiveID or logarchiveId, then we need to change the last backup time and set the reason for clearinig the backup time
			  --MR: 163985: Now bring back the code, it is enabled on key bEnablePopUpMessageDuringStoragePolicyChange
			  IF EXISTS(SELECT 1 FROM APP_AdvanceSettings WHERE keyName = 'bEnablePopUpMessageDuringStoragePolicyChange' AND relativePath = 'CommServDB.Console' AND type = 'BOOLEAN' AND cast([value] as nvarchar(32)) = N'true' AND enabled = 1 AND deleted = 0 AND entityType = 3 AND entityId = 2)
			  BEGIN
					UPDATE scp SET attrVal = N'0'
					FROM APP_SubClientProp scp
					INNER JOIN #bkpSetInfo BkpSet ON BkpSet.newSCId = scp.componentNameId AND scp.attrName IN('sys:last full','sys:last incr')  AND scp.modified=0
					WHERE BkpSet.dataArchGrpID <> @appdArchId
					MERGE APP_SubClientProp as Target
					USING
					(
						SELECT bkpset.newSCid componentNameId, N'Reason last backup time cleared' as attrName
						FROM #bkpSetInfo bkpset
						WHERE bkpset.dataArchGrpID <> @appdArchId
					)AS SOURCE
					ON(Target.componentNameId = Source.componentNameId AND Target.attrName = Source.attrName AND Target.modified=0)
					WHEN MATCHED THEN
						UPDATE SET Target.attrval = N'9'
					WHEN NOT MATCHED BY TARGET THEN
					INSERT(componentNameId,attrName,attrType,attrVal,created,modified)
						VALUES(Source.componentNameId,Source.attrName,7,9,@timeStamp,0)	;
			  END
UPDATE App  set App.dataArchGrpID = @appdArchId,  App.logArchGrpID = @applArchId, App.subclientName = @appscName , App.subclientStatus = (App.subclientStatus & 0x0100) | @appscStatus
			  FROM  APP_Application App
			  INNER JOIN #bkpsetinfo bkpset ON App.id = bkpset.newSCId
 			  --Create System State subclient
 			  INSERT INTO APP_Application (clientId,appTypeId,instance,backupSet,appNumber,dataArchGrpID,
					logArchGrpID,refTime,modified,subclientName,subclientStatus, origCCId, GUID)
				SELECT newClientId, newAppTypeId ,newInstanceId, backupsetid, @appappNumberId, @appdArchId,@applArchId, @timeStamp,   @timeStamp, @appscName, @appscStatus, 2, newid()
			    FROM #bkpSetInfo WHERE newScId=0
			  UPDATE #bkpSetInfo SET isSCInserted=1 WHERE  newScId=0
 			END
			ELSE
			BEGIN
 			  INSERT INTO APP_Application (clientId,appTypeId,instance,backupSet,appNumber,dataArchGrpID,
					logArchGrpID,refTime,modified,subclientName,subclientStatus, origCCId, GUID)
				SELECT newClientId, newAppTypeId ,newInstanceId, backupsetid, @appappNumberId, @appdArchId,
									@applArchId, @timeStamp,   @timeStamp, @appscName, @appscStatus, 2, newid() FROM #bkpSetInfo
			  UPDATE #bkpSetInfo SET isSCInserted=1 WHERE  newScId=0
			END
			--select @newSCId = id  from APP_Application where backupset = @i_id and subclientName = @appscName
			UPDATE bkpset SET newSCId = id  , BkpSet.dataArchGrpID = App.dataArchGrpID
			FROM #bkpSetInfo bkpset
			INNER JOIN App_Application App ON App.backupset=bkpset.backupsetId and subclientName = @appscName
			WHERE bkpset.isSCInserted=1
			--AGE OFF THE ALREADY DEFINED CONTENT AND FILTER OF THE SC
			UPDATE FF SET FF.modified = @timeStamp
			FROM APP_ScFilterFile FF
			INNER JOIN #bkpSetInfo bkpset ON bkpset.newSCId = FF.componentNameId AND FF.modified = 0
			SET @useSubclientPolicyRetention = ISNULL((SELECT attrVal FROM APP_SubclientProp WITH (NOLOCK) WHERE componentNameId = @appId AND cs_attrName = CHECKSUM(N'Subclient Policy Has Retention') AND attrName = N'Subclient Policy Has Retention' AND modified = 0), 0)
			IF @useSubclientPolicyRetention > 0
			BEGIN
				--AGE OFF THE ALREADY DEFINED OVERLAPPED PROPERTIES
				UPDATE SCP
					SET SCP.modified = @timeStamp
				FROM APP_SubClientProp SCP
				INNER JOIN #bkpSetInfo bkpset ON
					bkpset.newSCId = SCP.componentNameId
					AND SCP.modified = 0
				INNER JOIN APP_SubClientProp Prop2 WITH(NOLOCK) ON
					Prop2.componentnameId = @appId
					AND Prop2.attrName = SCP.attrName
					AND Prop2.cs_attrName = SCP.cs_attrName
					AND Prop2.modified = 0
				--AGE OFF "DEFAULT" PROPERTIES OF SCP THAT MAY NOT HAVE BEEN SET YET
				UPDATE SCP
					SET SCP.modified = @timeStamp
				FROM APP_SubClientProp SCP
				INNER JOIN #bkpSetInfo bkpset ON
					bkpset.newSCId = SCP.componentNameId
					AND SCP.modified = 0
				INNER JOIN APP_CCP_prop CCP WITH(NOLOCK) ON
					CCP.attrName = SCP.attrName
					AND CCP.cs_attrName = SCP.cs_attrName
					AND CCP.componentLevel = 50
			END
			ELSE
			BEGIN
				--AGE OFF THE ALREADY DEFINED OVERLAPPED PROPERTIES
				UPDATE SCP
					SET SCP.modified = @timeStamp
				FROM APP_SubClientProp SCP
				INNER JOIN #bkpSetInfo bkpset ON
					bkpset.newSCId = SCP.componentNameId
					AND SCP.modified = 0
				INNER JOIN APP_SubClientProp Prop2 WITH(NOLOCK) ON
					Prop2.componentnameId = @appId
					AND Prop2.attrName = SCP.attrName
					AND Prop2.cs_attrName = SCP.cs_attrName
					AND Prop2.modified = 0
				LEFT JOIN #DecoupledRetentionProperties RET ON
					Prop2.attrName = RET.attrName
				WHERE
					RET.attrName IS NULL
				--AGE OFF "DEFAULT" PROPERTIES OF SCP THAT MAY NOT HAVE BEEN SET YET
				UPDATE SCP
					SET SCP.modified = @timeStamp
				FROM APP_SubClientProp SCP
				INNER JOIN #bkpSetInfo bkpset ON
					bkpset.newSCId = SCP.componentNameId
					AND SCP.modified = 0
				INNER JOIN APP_CCP_prop CCP WITH(NOLOCK) ON
					CCP.attrName = SCP.attrName
					AND CCP.cs_attrName = SCP.cs_attrName
					AND componentLevel = 50
				LEFT JOIN #DecoupledRetentionProperties RET ON
					CCP.attrName = RET.attrName
				WHERE
					RET.attrName IS NULL
			END
			-- This means a new subclient was created. Retention defaults should be created for 10.0 and newer subclients
			-- @TODO - for now we have only 3. If this requirement comes up for some more props, need to create new SP for setting defaults
			BEGIN
				INSERT INTO APP_SubClientProp (componentNameId,attrName,attrType,attrVal,created,modified)
				SELECT newSCId, 'Keep items past their expiration', 2, '1', @timeStamp, 0 FROM #bkpSetInfo WHERE clientReleaseId >= 15 AND isSCInserted = 1 -- SIM_ID_GX_REL100
				-- Determine retention to set
				-- default retention is delete immediately non-laptop client
				-- laptop client default retention is 730
				INSERT INTO APP_SubClientProp (componentNameId,attrName,attrType,attrVal,created,modified)
				SELECT newSCId, 'Days to keep items past their expiration', 7,
				CASE (Client.status & 4096)-- laptop client
				WHEN 0 THEN 0
				ELSE 730
				END, @timeStamp, 0
				FROM #bkpSetInfo bkpset INNER JOIN App_Client Client ON bkpset.newClientId = Client.id
				WHERE clientReleaseId >= 15 AND isSCInserted = 1
				INSERT INTO APP_SubClientProp (componentNameId,attrName,attrType,attrVal,created,modified)
				SELECT newSCId, 'Archived expiration days', 7, '-1', @timeStamp, 0 FROM #bkpsetInfo
				WHERE clientReleaseId >= 15 AND isSCInserted = 1
			END
--
--
			DELETE APC
			FROM archpipeconfig APC
			INNER JOIN #bkpSetInfo bkpset on appNumber = newSCId
			--AGE OFF property 'Associated subclient Policy'
DECLARE @SCP_ASSOCIATED_SUBCLIENT_POLICY NVARCHAR(1024) = N'Associated subclient Policy'
			UPDATE sp
			SET modified = @timeStamp
			FROM APP_SubClientProp sp
			JOIN #bkpSetInfo bk ON sp.componentNameId = bk.newSCId AND sp.attrName = @SCP_ASSOCIATED_SUBCLIENT_POLICY AND sp.cs_attrName = CHECKSUM(@SCP_ASSOCIATED_SUBCLIENT_POLICY) AND sp.modified = 0
			--Insert the association id
			INSERT INTO APP_SubClientProp (componentNameId,attrName,attrType,attrVal,created,modified)
			SELECT newSCId, @SCP_ASSOCIATED_SUBCLIENT_POLICY , 10 , @appId, @timeStamp , 0 FROM #bkpSetInfo
			--Insert the sw comp and NW agent in archpipeconfig table
			Select @compress = compressWhere, @nwAgents = numNWAgents, @NWWriteSpeed = NWWriteSpeed  from archpipeconfig where appNumber = @appId
			IF (@@ROWCOUNT <> 0)
			BEGIN
				INSERT INTO archpipeconfig
				SELECT newSCId, @compress, @nwAgents, @NWWriteSpeed FROM #bkpSetInfo
			END
			FETCH GetSubclientsCursor INTO @appId, @appClientId, @appAppTypeId ,@appinstanceId, @appbackupsetId, @appappNumberId, @appdArchId,
									@applArchId, @apprefTime,   @appmodifiedTime, @appscName, @appscStatus
		END
		CLOSE GetSubclientsCursor
		DEALLOCATE GetSubclientsCursor
		--Insert the association id for backupset
		INSERT INTO APP_BackupSetProp (componentNameId,attrName,attrType,attrVal,created,modified)
			SELECT backupsetId, N'Associated subclient Policy' , 7 , @scpId, @timeStamp, 0 FROM #bkpSetInfo
	END
   select @@rowcount
   DROP TABLE #bkpSetInfo
--   set nocount off
-- Tell the AWK processor that there are no more input lines to scan
GO

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

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

insert into GXDBVersions values(2, 'AppAssocSubClientPolicy',  'v1.5.88.11.20.1', 'AppAssocSubClientPolicy', 'v1.5.88.11.20.1')
GO

