

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/APPGetActivityControlXMLV2.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='APPGetActivityControlXMLV2')
BEGIN
	print '>>> Drop Stored Procedure: APPGetActivityControlXMLV2 <<<'
	drop procedure APPGetActivityControlXMLV2
END
IF EXISTS (select * from GxQscripts where name='APPGetActivityControlXMLV2')
	delete from GxQscripts where name = 'APPGetActivityControlXMLV2'
GO

IF EXISTS (select * from GXDBVersions where aliasname='APPGetActivityControlXMLV2')
	delete from GXDBVersions where aliasname = 'APPGetActivityControlXMLV2'
GO
print '... Creating Procedure: APPGetActivityControlXMLV2'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure APPGetActivityControlXMLV2
  @useExtTempTable INT = 0,
  @xmlText XML OUTPUT
AS
  DECLARE @xmlStr xml
DECLARE	@commcellId		integer
DECLARE @clientId		integer
DECLARE @appTypeId		integer
DECLARE @clientGroupId	integer
DECLARE @subclientId	integer
DECLARE @mediaAgentId	integer
DECLARE @defaultCSID	integer = 2
DECLARE @setTag  integer=0
DECLARE @instanceID		INTEGER
-- IN
-- exec APPGetActivityControlXML '<entity clientId="2" appTypeId="42"/>'
--		OUT
--		<activityControlOptions activityType="1" enableActivityType="1" enableAfterADelay="0">
--          <dateTime time="0" TimeZoneId="0" TimeZoneName="" />
--		</activityControlOptions>
DECLARE @drInstanceID INTEGER
set @drInstanceID  =  ISNULL((select top 1 instance from APP_Application
where appTypeId= 1000),1)
DECLARE @drSubclientId INTEGER
SET @drSubclientId = (SELECT ID FROM APP_Application WHERE appTypeId = 1000)
IF @useExtTempTable = 1
BEGIN
	UPDATE #AppGetActivityControlXML_activityEntities_Ext
		SET commcellId = ISNULL(commcellId, 0),
			clientId = ISNULL(clientId, 1),
			appTypeId = ISNULL(appTypeId, 0),
			clientGroupId = ISNULL(clientGroupId, 0),
			subclientId = ISNULL(subclientId, @drSubclientId),
			mediaAgentId = ISNULL(mediaAgentId, 1),
			instanceId = ISNULL(instanceId, @drInstanceID)
	UPDATE #AppGetActivityControlXML_activityEntities_Ext
		SET commcellId = @defaultCSID
	WHERE commcellId = 0
	UPDATE T
		SET T.retXML = (select
							(case JM.opType
							  WHEN     4 then    1	-- Backup
							  WHEN     5 then    2	-- Restore
							  WHEN    13 then    4	-- Aux Copy
							  WHEN    11 then    8	-- DR Backup
							  WHEN    10 then   16	-- Archive Prune
							  WHEN     9 then   32	-- Media Recycle
							  WHEN    14 then   64	-- Synthetic Full
							  WHEN     1 then  128  -- All Activity
							  WHEN    28 then  256	-- Schedule
							  WHEN    57 then  512	-- ContentIndexin (online?)
							  WHEN    53 then 1024	-- ContentIndexing Offline
							  WHEN   100 then 2048	-- DDB Activity
							  WHEN   31  then 8192  -- Data Verification
							  ELSE JM.opType * -1
							  END) as '@activityType',
							 (CASE JM.action
							  when 2 then 1
							  else 0
							 END) as '@enableActivityType',
							 (CASE JM.action
							  WHEN 1 then JM.reason
							 END) as '@reason',
							(case JM.UNCenableTime
								when 0 then 0
								else 1
								End) as '@enableAfterADelay',
							CASE WHEN JM.UNCenableTime <> 0
							THEN
								(select
								CONVERT(nvarchar,dbo.GetDateTime(JM.enableTime),20)
								as '@timeValue',
								JM.UNCenableTime as '@time',
								 JM.enableTimeZone as '@TimeZoneID',
								 --DATE_TIME = 56,
								 '56' as '@_type_',
								(Select TimeZoneStdName from SchedTimeZone where TimeZoneID = JM.enableTimeZone) as '@TimeZoneName'
								FOR XML PATH('dateTime'),TYPE)
							END
						FROM JMJobAction as JM WITH (NOLOCK)
						WHERE JM.clientId = T.clientId
							  AND JM.mediaAgentID	= T.mediaAgentId
							  AND JM.appType		= T.appTypeId
							  AND JM.clientGroupId = T.clientGroupId
							  AND JM.appId = T.subclientId
							  AND JM.instanceID = T.instanceID
						AND optype in ( 4, 5, 13, 11, 10, 9, 14, 1, 28, 57, 53, 100, 31)
						FOR XML PATH('activityControlOptions'),TYPE)
		FROM #AppGetActivityControlXML_activityEntities_Ext T
		UPDATE #AppGetActivityControlXML_activityEntities_Ext
		SET retXML = '<activityControlOptions activityType="1" enableActivityType="1" enableAfterADelay="0" /><activityControlOptions activityType="2" enableActivityType="1" enableAfterADelay="0" />'
		WHERE retXML IS NULL
		--Add Backup activity XML incase it is missing in JMJobAction table
		UPDATE #AppGetActivityControlXML_activityEntities_Ext
		SET retXML.modify('insert <activityControlOptions activityType="1" enableActivityType="1" enableAfterADelay="0" /> into (/)[1]')
		WHERE retXML.exist('/activityControlOptions[(@activityType) eq 1]') = 0
		--Add Restore activity XML incase it is missing in JMJobAction table
		UPDATE #AppGetActivityControlXML_activityEntities_Ext
		SET retXML.modify('insert <activityControlOptions activityType="2" enableActivityType="1" enableAfterADelay="0" /> into (/)[1]')
		WHERE retXML.exist('/activityControlOptions[(@activityType) eq 2]') = 0
		--Add Data Aging activity XML incase it is missing in JMJobAction table for Client and Client Group only
		UPDATE #AppGetActivityControlXML_activityEntities_Ext
		SET retXML.modify('insert <activityControlOptions activityType="16" enableActivityType="1" enableAfterADelay="0" /> into (/)[1]')
		WHERE (clientId > 1 OR clientGroupId > 0)
			AND appTypeId = 0
			AND mediaAgentId = 1
			AND retXML.exist('/activityControlOptions[(@activityType) eq 16]') = 0
END
ELSE
BEGIN
	IF @xmlText.exist('/CvEntities_GenericEntity')=1
	BEGIN
		SET @commcellId = ISNULL((SELECT  ref.value('@commcellId', 'int') AS client
								   FROM    @xmlText.nodes('CvEntities_GenericEntity') R ( ref )),0)
		set @clientId		=  ISNULL((SELECT  ref.value('@clientId', 'int') AS client
									FROM    @xmlText.nodes('CvEntities_GenericEntity') R ( ref )),1)
		set @appTypeId		=  ISNULL((SELECT  ref.value('@appTypeId', 'int') AS apptype
									FROM    @xmlText.nodes('CvEntities_GenericEntity') R ( ref )),
									ISNULL((SELECT  ref.value('@applicationId', 'int') AS apptype        -- in generic entity the tag for apptype is 'applicationId',so adding the tag check in case Generic entity is sent directly without modify tag to appTypeId
									FROM    @xmlText.nodes('CvEntities_GenericEntity') R ( ref )),0))
		set @clientGroupId	=  ISNULL((SELECT  ref.value('@clientGroupId', 'int') AS cg
									FROM    @xmlText.nodes('CvEntities_GenericEntity') R ( ref )),0)
		set @subclientId	=  ISNULL((SELECT  ref.value('@subclientId', 'int') AS sc
FROM    @xmlText.nodes('CvEntities_GenericEntity') R ( ref )),(SELECT ID FROM APP_Application WHERE appTypeId = 1000))
		set @mediaAgentId =  ISNULL((SELECT  ref.value('@mediaAgemtId', 'int') AS sc
									FROM    @xmlText.nodes('CvEntities_GenericEntity') R ( ref )),1)
		set @instanceID =  ISNULL((SELECT  ref.value('@instanceId', 'int') AS sc
									FROM    @xmlText.nodes('CvEntities_GenericEntity') R ( ref )),@drInstanceID)
		set @setTag =1
	END
	ELSE
	BEGIN
		SET @commcellId = ISNULL((SELECT  ref.value('@commcellId', 'int') AS client
								   FROM    @xmlText.nodes('entity') R ( ref )),0)
		set @clientId		=  ISNULL((SELECT  ref.value('@clientId', 'int') AS client
									FROM    @xmlText.nodes('entity') R ( ref )),1)
		set @appTypeId		=  ISNULL((SELECT  ref.value('@appTypeId', 'int') AS apptype
									FROM    @xmlText.nodes('entity') R ( ref )),0)
		set @clientGroupId	=  ISNULL((SELECT  ref.value('@clientGroupId', 'int') AS cg
									FROM    @xmlText.nodes('entity') R ( ref )),0)
		set @subclientId	=  ISNULL((SELECT  ref.value('@subclientId', 'int') AS sc
FROM    @xmlText.nodes('entity') R ( ref )),(SELECT ID FROM APP_Application WHERE appTypeId = 1000))
		set @mediaAgentId =  ISNULL((SELECT  ref.value('@mediaAgemtId', 'int') AS sc
									FROM    @xmlText.nodes('entity') R ( ref )),1)
		set @instanceID =  ISNULL((SELECT  ref.value('@instanceId', 'int') AS sc
									FROM    @xmlText.nodes('entity') R ( ref )),@drInstanceID)
	END
	IF @commcellId = 0						-- Per Anil P., 9.0 GUI may still send 0 for commcellId, so we need to "fix it" here
		SET @commcellId = @defaultCSID
	SET @xmlText =
		(select
			(case JM.opType
			  WHEN     4 then    1	-- Backup
			  WHEN     5 then    2	-- Restore
			  WHEN    13 then    4	-- Aux Copy
			  WHEN    11 then    8	-- DR Backup
			  WHEN    10 then   16	-- Archive Prune
			  WHEN     9 then   32	-- Media Recycle
			  WHEN    14 then   64	-- Synthetic Full
			  WHEN     1 then  128  -- All Activity
			  WHEN    28 then  256	-- Schedule
			  WHEN    57 then  512	-- ContentIndexin (online?)
			  WHEN    53 then 1024	-- ContentIndexing Offline
			  WHEN   100 then 2048	-- DDB Activity
			  WHEN   31  then 8192  -- Data Verification
			  ELSE JM.opType * -1
			  END) as '@activityType',
			 (CASE JM.action
			  when 2 then 1
			  else 0
			 END) as '@enableActivityType',
			 (CASE JM.action
			  WHEN 1 then JM.reason
			 END) as '@reason',
			(case JM.UNCenableTime
				when 0 then 0
				else 1
				End) as '@enableAfterADelay',
			CASE WHEN JM.UNCenableTime <> 0
			THEN
				(select
				CONVERT(nvarchar,dbo.GetDateTime(JM.enableTime),20)
				as '@timeValue',
				JM.UNCenableTime as '@time',
				 JM.enableTimeZone as '@TimeZoneID',
				 --DATE_TIME = 56,
				 '56' as '@_type_',
				(Select TimeZoneStdName from SchedTimeZone where TimeZoneID = JM.enableTimeZone) as '@TimeZoneName'
				FOR XML PATH('dateTime'),TYPE)
			END
		FROM JMJobAction as JM WITH (NOLOCK)
		WHERE JM.clientId = @clientId
			  AND JM.mediaAgentID	= @mediaAgentId  -- Always?
			  AND JM.appType		= @appTypeId
			  AND JM.clientGroupId = @clientGroupId
			  AND JM.appId = @subclientId
			  AND JM.instanceID = @instanceID
		AND optype in ( 4, 5, 13, 11, 10, 9, 14, 1, 28, 57, 53, 100, 31)
		FOR XML PATH('activityControlOptions'),TYPE)
		IF @xmlText IS NULL
			BEGIN
				SET @xmlText = '<activityControlOptions activityType="1" enableActivityType="1" enableAfterADelay="0" /><activityControlOptions activityType="2" enableActivityType="1" enableAfterADelay="0" />'
			END
		ELSE
			BEGIN
				--Add Backup activity XML incase it is missing in JMJobAction table
				IF @xmlText.exist('/activityControlOptions[(@activityType) eq 1]') = 0
				BEGIN
					SET @xmlText.modify('insert <activityControlOptions activityType="1" enableActivityType="1" enableAfterADelay="0" /> into (/)[1]')
				END
				--Add Restore activity XML incase it is missing in JMJobAction table
				IF @xmlText.exist('/activityControlOptions[(@activityType) eq 2]') = 0
				BEGIN
					SET @xmlText.modify('insert <activityControlOptions activityType="2" enableActivityType="1" enableAfterADelay="0" /> into (/)[1]')
				END
			END
	   --Add Data Aging activity XML incase it is missing in JMJobAction table for Client and Client Group only
	   IF (@clientId > 1 OR @clientGroupId > 0 ) and @appTypeId =0 and @mediaAgentId=1 and (@xmlText.exist('/activityControlOptions[(@activityType) eq 16]') = 0)
				BEGIN
					SET @xmlText.modify('insert <activityControlOptions activityType="16" enableActivityType="1" enableAfterADelay="0" /> into (/)[1]')
				END
		IF @setTag = 1
		SELECT @xmlText
		FOR XML PATH('App_EntityActivityControl')
END
	--select @xmlText
GO

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

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

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

