

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/TM_getTaskTimezones.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/TM_getTaskTimezones.sp,v $ $Id: TM_getTaskTimezones.sp,v 1.11.192.10.4.1 2021/06/28 23:29:05 rengu Exp $";
-- =============================================
-- Author:		Anand Vibhor
-- Create date: 04/15/2008
-- Description:	get the task time zone
-- =============================================
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='TM_getTaskTimezones')
	delete from GXDBVersions where aliasname = 'TM_getTaskTimezones'
GO
print '... Creating Procedure: TM_getTaskTimezones'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure TM_getTaskTimezones
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
--
--   PARAMETERS   &   OUTPUTS
--
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  @i_patternId integer
AS
  DECLARE @name nvarchar(1024);
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @taskId INT
DECLARE @subTaskId INT
DECLARE @taskFlags INT
DECLARE @useClientTimezone INT
DECLARE @isSchedulePolicyAssociatedToPlan INT = 0
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @taskType INT
DECLARE @patternFreqType INT
SELECT @taskId= T.taskId, @subTaskId = st.subTaskId,
	@isSchedulePolicyAssociatedToPlan = CASE
WHEN (t.flags & 536870912 ) <> 0		-- _PLAN_ASSOCIATED_ENTITY
			THEN 1
		ELSE 0
	END,
	@taskType = T.taskType
	FROM TM_Task AS T
                               INNER  join TM_SubTask st  on t.taskId = st.taskId
                                INNER JOIN TM_PatternAssoc pt on st.subTaskId = pt.subTaskId
                                where pt.patternId = @i_patternId
SELECT @useClientTimezone = P.tzId,@patternFreqType = P.freq_Type FROM TM_Pattern AS P where patternid = @i_patternId
DECLARE @csTimezone nvarchar(max) = dbo.GetClientTimezone(2) -- CS timezone.
----------------------------------
--GET ALL THE TIMEZONES OF PATTERN
----------------------------------
IF object_id('tempdb.dbo.#allClientsTable') is not null
	DROP TABLE #allClientsTable
IF object_id('tempdb.dbo.#timeZoneTbl') is not null
	DROP TABLE #timeZoneTbl
CREATE TABLE #timeZoneTbl (id INTEGER NOT NULL IDENTITY (1,1), timezoneName NVARCHAR(1024), jobStartTime INT DEFAULT -1)
CREATE TABLE #allClientsTable (appId INT, appTypeId INT, clientId INT, instanceId INT, backupsetId INT, status INT, bkpLevel INT ,timeZone NVARCHAR(1024) DEFAULT NULL, parentJobId INT DEFAULT 0, parentAppId INT DEFAULT 0)
CREATE CLUSTERED INDEX allClientsTable_appId_Index1 ON #allClientsTable ([appId])
-- Identify all clients and their timezones and pass to function
-- -- SP BODY BEGIN : TM_ComputeStartTimeForClientTbl.spb ----------------------------------------------------------------------------------------------------------------------------------
IF object_id('tempdb.dbo.#Clients_Timezone_StartTime') is NOT NULL
BEGIN
	DROP TABLE #Clients_Timezone_StartTime
END
CREATE TABLE #Clients_Timezone_StartTime(clientId INT, timezoneName NVARCHAR(MAX), jobStartTime INT DEFAULT -1, taskID INT DEFAULT 0, patternID INT DEFAULT 0 )
CREATE CLUSTERED INDEX Clients_Timezone_StartTime_clientId_taskID_Index ON #Clients_Timezone_StartTime (clientId, taskID)
IF object_id('tempdb.dbo.#SubClients_Timezone_StartTime') is NOT NULL
BEGIN
	DROP TABLE #SubClients_Timezone_StartTime
END
CREATE TABLE #SubClients_Timezone_StartTime(appId INT, timezoneName NVARCHAR(MAX), jobStartTime INT DEFAULT -1, taskID INT DEFAULT 0)
CREATE CLUSTERED INDEX SubClients_Timezone_StartTime_appId_Index ON #SubClients_Timezone_StartTime (appId)
-- -- SP BODY END : TM_ComputeStartTimeForClientTbl.spb ------------------------------------------------------------------------------------------------------------------------------------
IF (@useClientTimezone = 1001) --USE CLIENT TIMEZONE
BEGIN
	--INSERT INTO #timeZoneTbl SELECT DISTINCT(DBO.GetClientTimeZone(2))
	--Fix for when the schedule is set to run at client time zone and the schedule policy association is set for all clients.
IF EXISTS (SELECT 1 FROM TM_AssocEntity WHERE taskId=@taskId AND clientId=0 AND assocType=2) --_ENTITY_TYPE_ALL_CLIENTS
	BEGIN
		INSERT INTO #allClientsTable(appId, appTypeId,clientId, instanceId, backupsetId,status,bkpLevel ,parentJobId,parentAppId)
		EXEC TM_GetBackupEntity @taskId, @subTaskId, null, 0
		-- Insert subclients for schedule policies associated to plan.
		IF @isSchedulePolicyAssociatedToPlan=1
		BEGIN
			INSERT INTO #SubClients_Timezone_StartTime(appId , timezoneName, taskId )
			SELECT DISTINCT appId, NULL, @taskId
			FROM #allClientsTable
WHERE appId>0 AND appTypeId=106
		END
	END
	ELSE
	BEGIN
		INSERT INTO #allClientsTable(clientId)
		SELECT CLIENTiD FROM APP_ClientGroupAssoc where clientgroupid in ( SELECT clientGroupId from TM_AssocEntity where taskId = @taskId)
		UNION
		SELECT CLIENTiD from TM_AssocEntity where taskId = @taskId and clientId <> 0
		UNION
		-- Add all clients for subclients associated to plan
		SELECT APP.clientId
		FROM TM_AssocEntity (NOLOCK)
		INNER JOIN APP_SubClientProp SCP WITH (READUNCOMMITTED) ON SCP.attrName = 'Associated Plan' AND SCP.cs_attrName = CHECKSUM(N'Associated Plan')
		AND CAST(TM_AssocEntity.entityId AS NVARCHAR(10)) = SCP.attrVal
		AND SCP.Modified = 0
		AND TM_AssocEntity.assocType = 158 --- plan entity
		AND TM_AssocEntity.taskID = @taskId
		INNER JOIN App_Application APP (NOLOCK)  ON APP.Id = SCP.componentNameId
		INSERT INTO #allClientsTable(clientId , timeZone)
		SELECT DISTINCT A.clientId,
			CASE
				WHEN tz.TimeZoneId IS NOT NULL THEN tz.TimeZoneStdName
				WHEN P.componentNameId IS NOT NULL THEN RIGHT(P.attrVal, CHARINDEX(':', REVERSE(P.attrVal)) - 1)
			END tzName
		FROM #allClientsTable E
		JOIN APP_Application A ON A.apptypeId = 106 AND A.clientId = E.clientId AND E.clientId <> 0
		LEFT OUTER JOIN APP_SubClientProp P ON P.componentNameId = A.id AND P.attrName = N'timezone'AND P.modified = 0 AND P.cs_attrName = CHECKSUM(N'timezone')
		LEFT OUTER JOIN APP_SubClientProp P2 ON P2.componentNameId = A.id AND P2.attrName = N'timezone id'AND P2.modified = 0 AND P2.cs_attrName = CHECKSUM(N'timezone id')
		LEFT OUTER JOIN SchedTimeZone tz ON P2.attrVal IS NOT NULL AND P2.attrVal = CAST(tz.TimeZoneID AS VARCHAR(12))
		IF @isSchedulePolicyAssociatedToPlan=1
		BEGIN
			INSERT INTO #SubClients_Timezone_StartTime(appId , timezoneName, taskId)
			SELECT DISTINCT App_application.Id, NULL, @taskId
			FROM #allClientsTable INNER JOIN App_application ON #allClientsTable.clientID = App_application.clientID
AND App_application.appTypeId=106
			AND (App_application.subclientstatus &4) = 0 -- CV_STATUS_DELETED
		END
	END
	INSERT INTO #Clients_Timezone_StartTime(clientId , timezoneName , jobStartTime , taskID , patternID )
	SELECT DISTINCT clientId, NULL, -1, @taskId, @i_patternId
	FROM #allClientsTable
	WHERE clientID>0
	-- Call stored procedure to compute timezone and start time for clients.
	EXEC TM_ComputeStarttimeForClient
	INSERT INTO #timeZoneTbl
	SELECT DISTINCT timezoneName,jobStartTime FROM #Clients_Timezone_StartTime
	WHERE timezoneName IS NOT NULL
	UNION
	SELECT #allClientsTable.timeZone, #Clients_Timezone_StartTime.jobStartTime
	FROM #allClientsTable -- To handle timezone set on VSA subclient
	INNER JOIN #Clients_Timezone_StartTime ON #Clients_Timezone_StartTime.clientID =  #allClientsTable.clientId
	WHERE #allClientsTable.timeZone IS NOT NULL
	UNION
	SELECT @csTimezone,-1
WHERE (NOT (@taskType = 2 AND (@patternfreqType & 1= 1 )) )
	-- For one time schedules using client timezone, if CS is in UTC timezone and client is in EST, then schedule pattern is marked deleted once runtime for UTC hits
	-- So runtime for client isnt executed and job isnt running for client.
	-- Adding CS timezone with jobStart time as -1 seperately to know the utc time when last job ran. So when subclient with new timezone is added we can compute next runtime correctly.
	UNION
	SELECT DISTINCT timezoneName,jobStartTime FROM #SubClients_Timezone_StartTime
	WHERE timezoneName IS NOT NULL AND jobStartTime>= 0
END
ELSE IF (@useClientTimezone = 1000)
BEGIN
	INSERT INTO #timeZoneTbl SELECT @csTimezone, -1
END
--WE WILL USE THE PATTERN TIMEZONE
ELSE
	INSERT INTO #timeZoneTbl SELECT timezoneStdName,-1 FROM SchedTimeZone where TimeZoneId = (SELECT tzID from TM_pattern where patternId = @i_patternId)
IF ((SELECT COUNT (*)  FROM #timeZoneTbl) = 0)
BEGIN
 INSERT INTO #timeZoneTbl SELECT @csTimezone,-1
END
UPDATE #timeZoneTbl SET timezoneName=@csTimezone WHERE timezoneName IS null
-- In case client has invalid non windows recognizable timezone, return CS timezone
UPDATE #timeZoneTbl SET timezoneName=@csTimezone WHERE timezoneName not in
(
	select timezoneStdName from SchedTimeZone
where (Flags & CONVERT(int, 0x1 )) <> 0x1
)
SELECT timezoneStdName,jobStartTime FROM
(
	SELECT DISTINCT SchedTimeZone.timezoneStdName , timeZoneTbl.jobStartTime, bias FROM SchedTimeZone
	INNER JOIN #timeZoneTbl timeZoneTbl ON timeZoneTbl.timezoneName = SchedTimeZone.TimeZoneStdName
AND (Flags & CONVERT(int, 0x1 )) <> 0x1
) T
ORDER BY bias
END
IF object_id('tempdb.dbo.#allClientsTable') is not null
	DROP TABLE #allClientsTable
IF object_id('tempdb.dbo.#timeZoneTbl') is not null
	DROP TABLE #timeZoneTbl
IF object_id('tempdb.dbo.#Clients_Timezone_StartTime') is NOT NULL
	DROP TABLE #Clients_Timezone_StartTime
GO

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

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

insert into GXDBVersions values(2, 'TM_getTaskTimezones',  'v1.11.192.10.4.1', 'TM_getTaskTimezones', 'v1.11.192.10.4.1')
GO

