
use control

/*
  * Reserves a Tieline2Path. The Tieline2Path_ID is passed in as a possible candidate. Verify that each segment is available.
  * Available meaning it's not being used or else we can piggy back on it.
  * Insert each segment if available. If an error is encounted or the segment isn't available then return. This procedure will
  * be called again with the next possible Tieline2Path_ID (if there is one).
  */
IF OBJECT_ID('NV_ReserveTieline2Path') IS NOT NULL
  DROP PROCEDURE NV_ReserveTieline2Path
GO

CREATE PROCEDURE NV_ReserveTieline2Path	
				@intNvUserIn integer,
				@vchIPAddressIn varchar(20),
				@vchLTCIn varchar(20),
				@intSrcPortIn integer,
				@intDestPortIn integer,
				@intTieline2PathIn integer ,
				@vchTielines  varchar(200) = ''  OUTPUT,
				@intStatus integer OUTPUT

AS

--PRINT 'Entering NV_ReserveTieline2Path'

SET NOCOUNT ON;

declare @vchSrcCtrlSysName 		varchar(24)
declare @intSourcePhysLevel 	integer
declare @intSourceSignalType 	integer
declare @intSourceInputPort	 	integer
declare @intSourceOutputPort 	integer
declare @intSourceInputTieline	integer
declare @intSourceOutputTieline integer
declare @intSourceSegment		integer

declare @intDestPhysLevel 		integer
declare @intDestSignalType 		integer
declare @intDestInputPort 		integer
declare @intDestOutputPort  	integer
declare @intDestInputTieline	integer
declare @intDestOutputTieline	integer
declare @intDestSegment			integer

declare @intTieline2			integer
declare @intSuccess				integer
declare @intContinue			integer
declare @vchValue				varchar(200)
declare @dtmCurrentTimestamp 	datetime		-- We want all the segments to have the same timestamp
declare @intHopCount			integer			-- Which hop are we are, count starts at 1
declare @intSourceChanged		integer

SET @dtmCurrentTimestamp = GETDATE()
SET @intSuccess = 0
SET @intContinue = 1
SET @intHopCount = 0
SET @intSourceChanged = 0

/* 
 * The Source's control system name is stored in the reservation table at this point we're the source
 * so use our control system's name. If another control system is requesting to reserve a tieline segment
 * then a stored procedure on that control system's machine calls NV_ReserveTieline2Segment directly passing
 * in it's name
 */
SET @vchSrcCtrlSysName = ( SELECT ControlSystem_Name FROM ControlSystem WHERE ControlSystem_ID = 1 )

DECLARE cursor_Tieline2PathDetail CURSOR LOCAL  FOR 
		
	SELECT Tieline2PathDetail_PhysLevel, Tieline2PathDetail_SignalType,
		Tieline2PathDetail_InputPort, Tieline2PathDetail_OutputPort, 
		Tieline2PathDetail_InputTieline, Tieline2PathDetail_OutputTieline, Tieline2PathDetail_SegmentIndex
	FROM Tieline2PathDetail
	WHERE
		Tieline2PathDetail_Tieline2Path = @intTieline2PathIn order by Tieline2PathDetail_SegmentIndex, Tieline2PathDetail_OutputPort
	
	OPEN cursor_Tieline2PathDetail
	
	FETCH NEXT FROM cursor_Tieline2PathDetail
		INTO @intSourcePhysLevel, @intSourceSignalType, @intSourceInputPort, @intSourceOutputPort, @intSourceInputTieline, @intSourceOutputTieline, @intSourceSegment
	IF ( @@FETCH_STATUS != 0 or @@error != 0 )
	BEGIN
		SET @intContinue = 0
		--PRINT '		No rows or error on initial fetch.'
	END
	
	-- Continue until we hit and error or else we haven't finished.
	WHILE ( @intContinue = 1 and @intSuccess = 0 ) 
	BEGIN
		
		FETCH NEXT FROM cursor_Tieline2PathDetail
			INTO @intDestPhysLevel, @intDestSignalType, @intDestInputPort, @intDestOutputPort, @intDestInputTieline, @intDestOutputTieline, @intDestSegment
		IF ( @@FETCH_STATUS != 0 or @@ERROR != 0 )
		BEGIN
			SET @intContinue = 0
			SET @intSuccess = 0
			--PRINT '		No rows or error on initial fetch.'
			BREAK
		END
			
		SET @intTieline2 = @intSourceOutputTieline
	
		-- Is this the end?
		IF ( @intDestOutputPort = -1 )
		BEGIN
			SET @intContinue = 0
			--PRINT 'Destination port equal to -1. We are done.'
		END

		EXEC NV_ConvertIntToString @intTieline2, @vchValue = @vchValue OUTPUT
		--PRINT 'Tieline2: ' + @vchValue
		
		--  If the one we fetched is -1 that means the source port is undefined, so pop in the real value. Do the same with
		-- the destination port.
		IF ( @intSourceInputPort = -1 )
			SET @intSourceInputPort = @intSrcPortIn
		IF ( @intDestOutputPort = -1)
			SET @intDestOutputPort = @intDestPortIn
			
		SET @intHopCount = @intHopCount + 1

		-- Reserve this segment, reuse if possible.
		--PRINT '	Calling NV_ReserveTieline2Segement...'
		
		EXEC @intStatus = NV_ReserveTieline2Segment
				@intNvUserIn,
				@vchIPAddressIn,
				@vchLTCIn,
				@intTieline2,
				@vchSrcCtrlSysName,
				@intSourcePhysLevel, 
				@intSourceSignalType,
				@intSourceInputPort,
				@intDestPhysLevel, 
				@intDestSignalType,
				@intDestOutputPort,
				@intDestInputPort,		-- Downstream
				@intSourceOutputPort,  -- Upstream
				@dtmCurrentTimestamp,
				@intHopCount,
				@intSuccess OUTPUT
				
		--PRINT '	NV_ReserveTieline2Segement returned intSuccess: ' + CAST(@intSuccess AS VARCHAR(6)) + ', intStatus: ' + --CAST(@intStatus AS VARCHAR(6))

		/* Meanings of intSucess and intStatus values
		 @intSuccess = 1 and @intStatus = 0		-- segment reserved, nothing special happened when reserving segment
		 @intSuccess = 1 and @intStatus = 77	-- use existing segment, but a warning that this segment is being shared.
		 @intSuccess = 1 and @intStatus = 88	-- segment reserved, but the source port is different and has been changed
		 @intSuccess = 1 and @intStatus = 66	-- segment reserved, but Destination is different
		 @intSuccess = 0 and @intStatus = 99    -- Different source, same destination, but tieline is shared. We need to delete the old tieline and find a new one
		*/
		
		-- One more check. If the source has been changed and the destination is different then we can't use
		-- this path, this is only a problem for multihop tielines. If the source and destination are different
		-- for a one hop tieline this problem will be caught in NV_ReserveTieline2Segement, but for multihop we
		-- need flag the source changed at this level. Also a problem is the source has been changed, and a down stream
		-- segment is shared.
		IF ( @intSourceChanged = 1 and ( @intStatus = 66 or @intStatus = 77 ) )
		BEGIN
			-- Source and destination are different, can't use this path
			SET @intSuccess = 0
		END 

		IF ( @intSuccess = 1 )
		BEGIN
			
			IF ( @intStatus = 88 ) 
				SET @intSourceChanged = 1

			--PRINT 'We have successfully reserved (or reused) the segment'
			-- We've reserved this segment. Save the information to return to the caller and set us up for the next fetch.
			IF ( @vchTielines != '' )
				SET @vchTielines = @vchTielines+', '
			SET @vchTielines = @vchTielines + CAST(@intTieline2 AS VARCHAR(6)) + ', ' + CAST(@intSourceOutputPort AS VARCHAR(6)) + ', ' + CAST(@intSourcePhysLevel AS VARCHAR(6))
									+ ', ' + CAST(@intDestInputPort AS VARCHAR(6)) + ', ' + CAST(@intDestPhysLevel AS VARCHAR(6))
									
			-- If we aren't done then destination is now the source on the next TielinePathDetail segment
			IF ( @intContinue = 1 )	
			BEGIN
				SET @intSourcePhysLevel		= @intDestPhysLevel
				SET @intSourceSignalType	= @intDestSignalType
				SET @intSourceInputPort		= @intDestInputPort
				SET @intSourceOutputPort	= @intDestOutputPort
				SET @intSourceInputTieline	= @intDestInputTieline
				SET @intSourceOutputTieline	= @intDestOutputTieline
				SET @intSourceSegment 		= @intDestSegment
				
				--Reset success back to 0, or else we'll exit the loop
				SET @intSuccess = 0
			END
			--ELSE
			--BEGIN
				--PRINT 'We are done'
			--END
		END
			
		ELSE
		BEGIN
			--This segment is already in use so we can't use this path or else a combination of the user, source port or destination port has changed enough
			--where we need to try another tieline path.
			
			SET @intContinue = 0
			--PRINT 'Segment in use.'

			IF ( @intStatus = 99 ) 
			BEGIN
				--Delete this segment.
				--Do a rollback incase any other segments have been reserved. 

				IF (@@trancount > 0 )
				BEGIN
					--PRINT 'Before deleting row we are rolling back transaction'
					ROLLBACK tran p1
					--PRINT 'After rollback, @error: ' + + CONVERT(VARCHAR, @@error)
					--PRINT '                @@trancount: ' + CAST(@@trancount AS VARCHAR(6))
					
					-- Now start a new one
					--PRINT 'Before deleting row start a new transaction'
					BEGIN tran p1
					--PRINT 'After begin trans, @error: ' + + CONVERT(VARCHAR, @@error)
					--PRINT '                  @@trancount: ' + CAST(@@trancount AS VARCHAR(6))
				END

				-- If this Tieline is on a remote ControlSystem then it's been reserved on that system
				-- execute a delete remotely.

	
				DELETE FROM Tieline2Reservation 
				WHERE 
					Tieline2Reservation_Tieline2 = @intTieline2 and
					Tieline2Reservation_User = @intNvUserIn and
					Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevel and
					Tieline2Reservation_DestinationPort = @intDestOutputPort

				--PRINT 'Deleting row. @error: ' + CONVERT(VARCHAR, @@error)
				--PRINT '              @@trancount: ' + CAST(@@trancount AS VARCHAR(6))
			END 
		END

	END

		
CLOSE cursor_Tieline2PathDetail
DEALLOCATE cursor_Tieline2PathDetail 

--PRINT 'Just before exiting NV_ReserveTieline2Path @@trancount: ' + CAST(@@trancount AS VARCHAR(6))
--PRINT '                                            @intSuccess: ' + CAST(@intSuccess AS VARCHAR(6))
--PRINT '                                            @intStatus: ' + CAST(@intStatus AS VARCHAR(6))

-- Don't send back partial information, if we couldn't reserve the entire Tieline2Path
IF ( @intSuccess != 1 )
	SET @vchTielines = ''
	
--PRINT 'EXITING NV_ReserveTieline2Path'

RETURN @intSuccess

