SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

UPDATE REF_LINK_TABLES
SET ReadOnly = 0
WHERE Table_Name = 'LNK_JOB_CONTRACT_VIRTUAL'
GO

DROP FUNCTION [dbo].[fn_Job_Contract]
GO

SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[fn_Job_Contract](@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Right_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Right_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Right_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Right_Join, ChildSeq, Left_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT  CAST(ISNULL(Job, 0) AS int) AS Left_Join
							, Entity_Id AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id, CAST(Job AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_CONTRACT] OuterQuery
					WHERE NOT CAST(ISNULL(Job, 0) AS int) = 0

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Right_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Right_Join = OuterQuery.Right_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Right_Join = OuterQuery.Right_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Right_Join = OuterQuery.Right_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Right_Join = OuterQuery.Right_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1

			WHERE  (OuterQuery.Left_Join = @Left_Join OR @Left_Join IS NULL)
					AND (OuterQuery.Right_Join = @Right_Join OR  @Right_Join IS NULL)

		) a
			
)


GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER TRIGGER [dbo].[UpdateJobVirtualLinks] ON [dbo].[DT_JOB] 
FOR INSERT, UPDATE, DELETE 
AS

BEGIN

	SET NOCOUNT ON

	DECLARE @ChangeThreshold int
	DECLARE @RefreshAll bit
	DECLARE @Processed nvarchar(1024)
	DECLARE @CurrentID nvarchar(32)
	DECLARE @AlreadyDone bit
	DECLARE @Entity_Id int
	DECLARE @PayID int
	DECLARE @ContractID int
	DECLARE @JobLoop int
	DECLARE @FullRecalc bit
	DECLARE @Action as char(1)
	DECLARE @Cinfo VARBINARY(128)
	DECLARE @PayPayGrade smallint

	--this session flag is used to disable a trigger temporarily
	SELECT @Cinfo = Context_Info() 
	IF @Cinfo = 0x55555 
		RETURN 

	SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
							AND EXISTS(SELECT * FROM DELETED)
						THEN 'U'  -- Set Action to Updated.
						WHEN EXISTS(SELECT * FROM INSERTED)
						THEN 'I'  -- Set Action to Insert.
						WHEN EXISTS(SELECT * FROM DELETED)
						THEN 'D'  -- Set Action to Deleted.
						ELSE NULL -- Skip. It may have been a "failed delete".   
					END)

	IF (@Action IS NULL)
		RETURN;

	IF (@Action = 'I' OR @Action = 'D')
		SET @FullRecalc = 1

	IF (@Action = 'U')
		--Entity fields always recalc
		IF (UPDATE(Entity_Id) OR UPDATE(Entity_Start_Date) OR UPDATE(Entity_End_Date))
			SET @FullRecalc = 1

	SET @PayPayGrade = ISNULL((SELECT TOP 1 Enabled FROM REF_LINK_TABLES
						WHERE Table_name = 'LNK_PAY_PAY_GRADE_VIRTUAL'
						AND CustomisationID IN (SELECT CustomisationID FROM REF_CUSTOMISATIONS WHERE Enabled =1)
						ORDER BY CustomisationID DESC), 0)

	DECLARE @PayChangeCount int

	SET @ChangeThreshold = 5

	IF ((SELECT DISTINCT COUNT(Entity_Id) FROM INSERTED) + (SELECT DISTINCT COUNT(Entity_Id) FROM DELETED) > @ChangeThreshold)
		SET @RefreshAll = 1

	IF (@RefreshAll = 1)
		BEGIN

			IF (@FullRecalc = 1)
			BEGIN		
				DELETE FROM LNK_JOB_CONTRACT_VIRTUAL
				DBCC CHECKIDENT (LNK_JOB_CONTRACT_VIRTUAL, RESEED, 1)
				INSERT INTO LNK_JOB_CONTRACT_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
				SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Job_Contract(NULL, NULL)				
			END
			
			IF (@FullRecalc = 1 OR UPDATE(Location))
			BEGIN		
				DELETE FROM LNK_JOB_LOCATION_VIRTUAL
				DBCC CHECKIDENT (LNK_JOB_LOCATION_VIRTUAL, RESEED, 1)
				INSERT INTO LNK_JOB_LOCATION_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
				SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Job_Location(NULL, NULL)

				DELETE FROM LNK_CONTRACT_LOCATION_VIRTUAL
				DBCC CHECKIDENT (LNK_CONTRACT_LOCATION_VIRTUAL, RESEED, 1)
				INSERT INTO LNK_CONTRACT_LOCATION_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
				SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Contract_Location(NULL, NULL)

			END
			
			IF (@FullRecalc = 1 OR UPDATE(Department))
			BEGIN		
				DELETE FROM LNK_JOB_DEPARTMENT_VIRTUAL
				DBCC CHECKIDENT (LNK_JOB_DEPARTMENT_VIRTUAL, RESEED, 1)
				INSERT INTO LNK_JOB_DEPARTMENT_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
				SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Job_Department(NULL, NULL)

				DELETE FROM LNK_CONTRACT_DEPARTMENT_VIRTUAL
				DBCC CHECKIDENT (LNK_CONTRACT_DEPARTMENT_VIRTUAL, RESEED, 1)
				INSERT INTO LNK_CONTRACT_DEPARTMENT_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
				SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Contract_Department(NULL, NULL)
			END
					
			IF (@FullRecalc = 1 OR UPDATE(Pay_Grade))
			BEGIN		
				DELETE FROM LNK_JOB_PAY_GRADE_VIRTUAL
				DBCC CHECKIDENT (LNK_JOB_PAY_GRADE_VIRTUAL, RESEED, 1)
				INSERT INTO LNK_JOB_PAY_GRADE_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
				SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Job_Pay_Grade(NULL, NULL)

				DELETE FROM LNK_PAY_PAY_GRADE_VIRTUAL
				DBCC CHECKIDENT (LNK_PAY_PAY_GRADE_VIRTUAL, RESEED, 1)
				INSERT INTO LNK_PAY_PAY_GRADE_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
				SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Pay_Pay_Grade(NULL, NULL)

			END
			
			IF (@FullRecalc = 1 OR UPDATE(Contract_Type))
			BEGIN		
				DELETE FROM LNK_JOB_CONTRACT_TYPE_VIRTUAL
				DBCC CHECKIDENT (LNK_JOB_CONTRACT_TYPE_VIRTUAL, RESEED, 1)
				INSERT INTO LNK_JOB_CONTRACT_TYPE_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
				SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Job_Contract_Type(NULL, NULL)

				DELETE FROM LNK_CONTRACT_CONTRACT_TYPE_VIRTUAL
				DBCC CHECKIDENT (LNK_CONTRACT_CONTRACT_TYPE_VIRTUAL, RESEED, 1)
				INSERT INTO LNK_CONTRACT_CONTRACT_TYPE_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
				SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Contract_Contract_Type(NULL, NULL)
			END
					
		END
	ELSE
		BEGIN
			
			SET @Processed = ''
						
			DECLARE ChangesCursor CURSOR LOCAL FAST_FORWARD 
			FOR SELECT DISTINCT Entity_Id FROM INSERTED WHERE Entity_Id > 0 UNION SELECT DISTINCT Entity_Id FROM DELETED WHERE Entity_Id > 0
			OPEN ChangesCursor

			FETCH NEXT FROM ChangesCursor INTO @Entity_Id
			
 			SET @JobLoop = @@FETCH_STATUS

			WHILE @JobLoop = 0
			BEGIN
				SET @CurrentID =  ' ' + CAST(@Entity_Id as nvarchar) + ' '

				SET @AlreadyDone =  CASE (PATINDEX(@CurrentID, @Processed)) WHEN 0 THEN 0 ELSE 1 END
				
				IF @AlreadyDone = 0
				BEGIN
					IF (@FullRecalc = 1)
					BEGIN		
						DELETE FROM LNK_JOB_CONTRACT_VIRTUAL WHERE Left_Join = @Entity_Id
						INSERT INTO LNK_JOB_CONTRACT_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
						SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Job_Contract(@Entity_Id, NULL)
					END

					IF (@FullRecalc = 1 OR UPDATE(Location))
					BEGIN							
						DELETE FROM LNK_JOB_LOCATION_VIRTUAL WHERE Left_Join = @Entity_Id
						INSERT INTO LNK_JOB_LOCATION_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
						SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Job_Location(@Entity_Id, NULL)
					END
					
					IF (@FullRecalc = 1 OR UPDATE(Department))
					BEGIN		
						DELETE FROM LNK_JOB_DEPARTMENT_VIRTUAL WHERE Left_Join = @Entity_Id
						INSERT INTO LNK_JOB_DEPARTMENT_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
						SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Job_Department(@Entity_Id, NULL)
					END
									
					IF (@FullRecalc = 1 OR UPDATE(Pay_Grade))
					BEGIN		
						DELETE FROM LNK_JOB_PAY_GRADE_VIRTUAL WHERE Left_Join = @Entity_Id
						INSERT INTO LNK_JOB_PAY_GRADE_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
						SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Job_Pay_Grade(@Entity_Id, NULL)
					END
											
					IF (@FullRecalc = 1 OR UPDATE(Contract_Type))
					BEGIN		
						DELETE FROM LNK_JOB_CONTRACT_TYPE_VIRTUAL WHERE Left_Join = @Entity_Id
						INSERT INTO LNK_JOB_CONTRACT_TYPE_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
						SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Job_Contract_Type(@Entity_Id, NULL)
					END
					
					--5.11 made virtuals between contract and dept/loc etc
					DECLARE ContractCursor CURSOR LOCAL FAST_FORWARD
					FOR SELECT DISTINCT DTC.Entity_Id
						FROM DT_CONTRACT DTC
						WHERE CAST(DTC.Job as int) = @CurrentID
					OPEN ContractCursor

					FETCH NEXT FROM ContractCursor INTO @ContractID
					
					WHILE  @@FETCH_STATUS = 0
					BEGIN

						IF (@FullRecalc = 1 OR UPDATE(Location))
						BEGIN							
							DELETE FROM LNK_CONTRACT_LOCATION_VIRTUAL WHERE Left_Join = @ContractID
							INSERT INTO LNK_CONTRACT_LOCATION_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
							SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Contract_Location(@ContractID, NULL)
						END
											
						IF (@FullRecalc = 1 OR UPDATE(Department))
						BEGIN							
							DELETE FROM LNK_CONTRACT_DEPARTMENT_VIRTUAL WHERE Left_Join = @ContractID
							INSERT INTO LNK_CONTRACT_DEPARTMENT_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
							SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Contract_Department(@ContractID, NULL)
						END

						IF (@FullRecalc = 1 OR UPDATE(Contract_Type))
						BEGIN		
							DELETE FROM LNK_CONTRACT_CONTRACT_TYPE_VIRTUAL WHERE Left_Join = @ContractID
							INSERT INTO LNK_CONTRACT_CONTRACT_TYPE_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
							SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Contract_Contract_Type(@ContractID, NULL)
						END
												
						FETCH NEXT FROM ContractCursor INTO @ContractID
					END

					CLOSE ContractCursor
					DEALLOCATE ContractCursor

					IF (@PayPayGrade = 1 AND (@FullRecalc = 1 OR UPDATE(Pay_Grade)))
					BEGIN
						SET @PayChangeCount = (SELECT COUNT(LPC.Left_Join) FROM LNK_PAY_CONTRACT LPC WITH (NOLOCK) 
									INNER JOIN DT_CONTRACT DTC WITH (NOLOCK) 
									ON LPC.Right_Join = DTC.Entity_Id
									INNER JOIN (SELECT DISTINCT Entity_Id FROM INSERTED  WHERE Entity_Id > 0 UNION SELECT DISTINCT Entity_Id FROM DELETED WHERE Entity_Id > 0) JobChanges
									ON CAST(DTC.Job as int) = JobChanges.Entity_Id)
											
						IF (@PayChangeCount BETWEEN 1 AND (@ChangeThreshold * 10))
						BEGIN
							DECLARE PayCursor CURSOR LOCAL FAST_FORWARD
							FOR SELECT DISTINCT LPC.Left_Join FROM LNK_PAY_CONTRACT LPC WITH (NOLOCK) 
								INNER JOIN DT_CONTRACT DTC WITH (NOLOCK) 
								ON LPC.Right_Join = DTC.Entity_Id
								AND CAST(DTC.Job as int) = @CurrentID
							OPEN PayCursor
		
							FETCH NEXT FROM PayCursor INTO @PayID
							
							WHILE  @@FETCH_STATUS = 0
							BEGIN
								DELETE FROM LNK_PAY_PAY_GRADE_VIRTUAL WHERE Left_Join = @PayID
								INSERT INTO LNK_PAY_PAY_GRADE_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
								SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Pay_Pay_Grade(@PayID, NULL)
								FETCH NEXT FROM PayCursor INTO @PayID
							END
		
							CLOSE PayCursor
							DEALLOCATE PayCursor
						END
					END
					
					SET @Processed = @Processed + @CurrentID
				END

				FETCH NEXT FROM ChangesCursor INTO @Entity_Id

				SET @JobLoop = @@FETCH_STATUS
			END

			CLOSE ChangesCursor
			DEALLOCATE ChangesCursor

			IF (@PayPayGrade = 1 AND (@FullRecalc = 1 OR UPDATE(Pay_Grade)))
			BEGIN
				IF (@PayChangeCount > (@ChangeThreshold * 10))
				BEGIN
					DELETE FROM LNK_PAY_PAY_GRADE_VIRTUAL
					DBCC CHECKIDENT (LNK_PAY_PAY_GRADE_VIRTUAL, RESEED, 1)
					INSERT INTO LNK_PAY_PAY_GRADE_VIRTUAL(Left_Join, Right_Join, Start_Date, End_Date)
					SELECT Left_Join, Right_Join, Start_Date, End_Date FROM fn_Pay_Pay_Grade(NULL, NULL)
				END
			END
			
		END

END




GO
SET QUOTED_IDENTIFIER ON
GO