/*
 * Trigger used to create tieline reservations for other tiellines in a tieline grouping.
 * 
 * If there's already a tieilne entry based on Tieline2_ID, NvUser_ID and ControlSystem_Name, then a
 * new entry isn't made.
 */

use control

IF EXISTS (SELECT name FROM sysobjects WHERE NAME='NV_ReserveTieline2GroupTrigger')
    DROP TRIGGER NV_ReserveTieline2GroupTrigger
go

CREATE TRIGGER NV_ReserveTieline2GroupTrigger
ON Tieline2Reservation

FOR INSERT
AS

SET NOCOUNT ON;

--DECLARE @vcharDebugStmt varchar(200)
--SET @vcharDebugStmt = 'echo Entering NV_ReserveTieline2GroupTrigger  >> c:\SQLDebugFile'
--EXEC master..xp_cmdshell @vcharDebugStmt

DECLARE @intTieline2Reservation_Tieline2 integer
SET @intTieline2Reservation_Tieline2 = (SELECT Tieline2Reservation_Tieline2 FROM Inserted)

DECLARE @dtTieline2Reservation_Timestamp DATETIME
SET @dtTieline2Reservation_Timestamp = (SELECT Tieline2Reservation_Timestamp FROM Inserted)

DECLARE @vcharTieline2Reservation_ControlSystem_Name varchar(24)
SET @vcharTieline2Reservation_ControlSystem_Name = (SELECT Tieline2Reservation_ControlSystem_Name FROM Inserted)

DECLARE @intTieline2Reservation_User integer
SET @intTieline2Reservation_User = (SELECT Tieline2Reservation_User FROM Inserted)

DECLARE @vcharTieline2Reservation_IPAddress varchar (20)
SET @vcharTieline2Reservation_IPAddress = (SELECT Tieline2Reservation_IPAddress FROM Inserted)

DECLARE @vcharTieline2Reservation_LTC varchar(20)
SET @vcharTieline2Reservation_LTC = (SELECT Tieline2Reservation_LTC FROM Inserted)

--SET @vcharDebugStmt = 'echo In NV_ReserveTieline2GroupTrigger processing inserted.Tieline2Reservation_Tieline2: ' + CAST( @intTieline2Reservation_Tieline2 AS VARCHAR(6) ) + ' >> c:\SQLDebugFile'
--EXEC master..xp_cmdshell @vcharDebugStmt 

/* Is this Tieline in a Group? Assuption: A Tieline can only be in one group */
DECLARE @intCount integer
SELECT @intCount = COUNT(*) FROM Tieline2GroupBinding WHERE Tieline2GroupBinding_Tieline2 = @intTieline2Reservation_Tieline2
IF ( @intCount = 1 )
BEGIN
	 

  DECLARE @intTieline2GroupBinding_Tieline2Group int
  DECLARE @intTieline2GroupBinding_Tieline2		 int
  
  SELECT @intTieline2GroupBinding_Tieline2Group = Tieline2GroupBinding_Tieline2Group FROM Tieline2GroupBinding WHERE Tieline2GroupBinding_Tieline2 = @intTieline2Reservation_Tieline2
  
  --SET @vcharDebugStmt = 'echo In NV_ReserveTieline2GroupTrigger processing TielineGroup: ' + CAST( @intTieline2GroupBinding_Tieline2Group AS VARCHAR(6) ) + ' >> c:\SQLDebugFile'
  --EXEC master..xp_cmdshell @vcharDebugStmt 
  
  DECLARE @intDestPhysLevel  int
  DECLARE @intDestSignalType int
  DECLARE @intDownPort 		 int
  DECLARE @intSrcPhysLevel   int
  DECLARE @intSrcSignalType  int
  DECLARE @intUpPort 		 int
  

  DECLARE cursorTielineGroup  CURSOR  LOCAL FOR 
		SELECT Tieline2GroupBinding_Tieline2
	 	FROM Tieline2GroupBinding
		WHERE
			Tieline2GroupBinding_Tieline2Group = @intTieline2GroupBinding_Tieline2Group AND
			Tieline2GroupBinding_Tieline2 <> @intTieline2Reservation_Tieline2
			order by Tieline2GroupBinding_Tieline2
	
	OPEN cursorTielineGroup
	
	FETCH NEXT FROM cursorTielineGroup INTO @intTieline2GroupBinding_Tieline2
	
	WHILE (  @@FETCH_STATUS = 0 )
	BEGIN
			--SET @vcharDebugStmt = 'echo In NV_ReserveTieline2GroupTrigger processing Grouped Tieline: ' + CAST( @intTieline2GroupBinding_Tieline2 AS VARCHAR(6) ) + ' >> c:\SQLDebugFile'
			--EXEC master..xp_cmdshell @vcharDebugStmt
			
			/* 
			 * Don't try to add a new tieline entry for a minor tieline if we've already got one for this user.
			 */
			SELECT @intCount = COUNT(*) FROM Tieline2Reservation 
				WHERE Tieline2Reservation_Tieline2 = @intTieline2GroupBinding_Tieline2 AND 
				 	  Tieline2Reservation_User = @intTieline2Reservation_User AND
				 	  Tieline2Reservation_ControlSystem_Name like @vcharTieline2Reservation_ControlSystem_Name
			IF ( @intCount = 0 )
			BEGIN
				--SET @vcharDebugStmt = 'echo In NV_ReserveTieline2GroupTrigger processing insert of Grouped Tieline: ' + CAST( @intTieline2GroupBinding_Tieline2 AS VARCHAR(6) ) + ' >> c:\SQLDebugFile'
				--EXEC master..xp_cmdshell @vcharDebugStmt
				
				SET @intDestPhysLevel = 0
				SET @intDestSignalType = 0;
				SET @intDownPort = 0
				
				SELECT TOP 1 @intDestPhysLevel = Tieline2PathDetail_PhysLevel 
				FROM Tieline2PathDetail where Tieline2PathDetail_InputTieline = @intTieline2GroupBinding_Tieline2
					
				-- Tielines can support multiple signal types, but it there's only one then use
				SELECT @intCount = COUNT( DISTINCT Tieline2PathDetail_SignalType ) 
					FROM Tieline2PathDetail where Tieline2PathDetail_InputTieline = @intTieline2GroupBinding_Tieline2
				IF ( @intCount = 1 )
					BEGIN
						SELECT TOP 1 @intDestSignalType = Tieline2PathDetail_SignalType 
					FROM Tieline2PathDetail where Tieline2PathDetail_InputTieline = @intTieline2GroupBinding_Tieline2
				END 
				
				-- If this is one to one or fan in then capture the downstream port
				SELECT @intCount = COUNT( DISTINCT Tieline2PathDetail_InputPort ) 
					FROM Tieline2PathDetail where Tieline2PathDetail_InputTieline = @intTieline2GroupBinding_Tieline2
				IF ( @intCount = 1 )
					BEGIN
						SELECT TOP 1 @intDownPort = Tieline2PathDetail_InputPort 
					FROM Tieline2PathDetail where Tieline2PathDetail_InputTieline = @intTieline2GroupBinding_Tieline2
				END 
				
				SET @intSrcPhysLevel = 0
				SET @intSrcSignalType = 0
				SET @intUpPort = 0
				
				SELECT TOP 1 @intSrcPhysLevel = Tieline2PathDetail_PhysLevel 
				FROM Tieline2PathDetail where Tieline2PathDetail_OutputTieline = @intTieline2GroupBinding_Tieline2
					
				-- Tielines can support multiple signal types, but it there's only one then use
				SELECT @intCount = COUNT( DISTINCT Tieline2PathDetail_SignalType ) 
					FROM Tieline2PathDetail where Tieline2PathDetail_OutputTieline = @intTieline2GroupBinding_Tieline2
				IF ( @intCount = 1 )
					BEGIN
						SELECT TOP 1 @intSrcSignalType = Tieline2PathDetail_SignalType 
					FROM Tieline2PathDetail where Tieline2PathDetail_OutputTieline = @intTieline2GroupBinding_Tieline2
				END 
				
				-- If this is one to one or fan out then capture the upstream port
				SELECT @intCount = COUNT( DISTINCT Tieline2PathDetail_OutputPort ) 
					FROM Tieline2PathDetail where Tieline2PathDetail_OutputTieline = @intTieline2GroupBinding_Tieline2
				IF ( @intCount = 1 )
					BEGIN
						SELECT TOP 1 @intUpPort = Tieline2PathDetail_OutputPort 
					FROM Tieline2PathDetail where Tieline2PathDetail_OutputTieline = @intTieline2GroupBinding_Tieline2
				END 
				
				--SET @vcharDebugStmt = 'echo In NV_ReserveTieline2GroupTrigger inserting Grouped Tieline: ' + CAST( @intTieline2GroupBinding_Tieline2 AS VARCHAR(6) ) + ' >> c:\SQLDebugFile'
				--EXEC master..xp_cmdshell @vcharDebugStmt
				
				INSERT into Tieline2Reservation ( 
					Tieline2Reservation_Tieline2, Tieline2Reservation_ControlSystem_Name, Tieline2Reservation_User, Tieline2Reservation_IPAddress,  Tieline2Reservation_LTC, Tieline2Reservation_Timestamp,
					Tieline2Reservation_SourcePhysLevel, Tieline2Reservation_SourceSignalType, Tieline2Reservation_SourcePort,  
					Tieline2Reservation_DestinationPhysLevel, Tieline2Reservation_DestinationSignalType, Tieline2Reservation_DestinationPort, 
					Tieline2Reservation_UpstreamPort, Tieline2Reservation_DownstreamPort ) 
						VALUES ( @intTieline2GroupBinding_Tieline2, @vcharTieline2Reservation_ControlSystem_Name, @intTieline2Reservation_User, 
							 @vcharTieline2Reservation_IPAddress, @vcharTieline2Reservation_LTC, @dtTieline2Reservation_Timestamp,
							 @intSrcPhysLevel,  @intSrcSignalType, 0, 
					 		 @intDestPhysLevel, @intDestSignalType, 0,
							 @intUpPort, @intDownPort)  

			END
			
			FETCH NEXT FROM cursorTielineGroup INTO @intTieline2GroupBinding_Tieline2
	END
	
	CLOSE cursorTielineGroup
	DEALLOCATE cursorTielineGroup

END

--SET @vcharDebugStmt = 'echo Exiting NV_ReserveTieline2GroupTrigger >> c:\SQLDebugFile'
--EXEC master..xp_cmdshell @vcharDebugStmt

GO