
use control

-- Found out if the user has any matching tielnes currently reserved  that can be used
-- or if we can piggy pack on the tieline of someone else. This currently is only supported 
-- for one hop solutions ( multihop is broken).

IF OBJECT_ID('NV_ReserveExistingTieline2Path') IS NOT NULL
  DROP PROCEDURE NV_ReserveExistingTieline2Path
GO

CREATE PROCEDURE NV_ReserveExistingTieline2Path 	
				@intNvUserIn 		integer,
				@vchIPAddressIn	 varchar(20),
				@vchLTCIn 		varchar(20),
				@intSrcPhysLevelIn 	 integer,
				@intSrcSignalTypeIn 	integer ,
				@intSrcPortIn		integer,
				@intDestPhysLevelIn 	integer ,
				@intDestSignalTypeIn	 integer ,
				@intDestPortIn		integer,
				@intTCinWhereClauseIn integer,
				@vchTielines  varchar(200) = ''  OUTPUT

AS

--PRINT ' Entering NV_ReserveExistingTieline2Path'
declare @vchValue	varchar(200)

SET NOCOUNT ON

declare @intTieline2 integer
declare @vchSrcCtrlSysName varchar(24)
declare @intUpPort integer
declare @intDownPort integer

declare @intSuccess	 integer
declare @intCount	 integer
declare @dtmCurrentTimestamp datetime		-- We want all the segments to have the same timestamp
declare @intHopCount integer

SET @dtmCurrentTimestamp = GETDATE()
SET @intHopCount = 1

SET @intSuccess = 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 )



/*
 * Let's be optimistic and look for a one hop solution. We're going to do this at most 3 times
 *
 * 1) matching source physlevel, source port, destination physlevel and destination port
 * 2) matching source physlevel, source port and destination physlevel
 * 3) matching source physlevel and destination physlevel
 */

DECLARE @vchSelectStmt nvarchar(2000)

DECLARE @intLoopCount int
SET @intLoopCount = 0

WHILE @intLoopCount <= 3 and @intSuccess = 0
BEGIN
    SET @intLoopCount = @intLoopCount + 1

	SET @vchSelectStmt = 
		'DECLARE cursor_Segment CURSOR FOR ' +
		'SELECT  ' +
			'Tieline2Reservation_Tieline2,  ' +
			'Tieline2Reservation_UpstreamPort,  ' +
			'Tieline2Reservation_DownstreamPort  ' +
		'FROM Tieline2Reservation  ' +
		'WHERE   ' +
			'Tieline2Reservation_SourcePhysLevel 		= ' + CAST(@intSrcPhysLevelIn AS VARCHAR(6)) + ' and  ' +
			'Tieline2Reservation_DestinationPhysLevel 	= ' + CAST(@intDestPhysLevelIn AS VARCHAR(6))
	------PRINT '	Select Stmt: ' + @vchSelectStmt

	-- Add source port to where clause?
	IF (@intLoopCount = 1 or @intLoopCount = 2 )
	BEGIN
		SET @vchSelectStmt = @vchSelectStmt + ' and Tieline2Reservation_SourcePort = ' + CAST(@intSrcPortIn AS VARCHAR(6)) 
		----PRINT '	Adding source port to where clause'
	END

	-- Add destination port to where clause?
	IF (@intLoopCount = 1 )
	BEGIN
		SET @vchSelectStmt = @vchSelectStmt + ' and Tieline2Reservation_DestinationPort = ' + CAST(@intDestPhysLevelIn AS VARCHAR(6)) 
		----PRINT '	Adding destination port to where clause'
	END

	-- Add timecode to where clause?
	IF ( @intTCinWhereClauseIn > 0 )
	BEGIN
		SET @vchSelectStmt = @vchSelectStmt + ' and Tieline2Reservation_LTC = ''' + @vchLTCIn + ''''
		----PRINT '	Adding TC to where clause'
	END
		
	------PRINT '	Select Stmt: ' + @vchSelectStmt

	EXEC sp_executesql @vchSelectStmt;
     
	OPEN cursor_Segment
	
	FETCH NEXT FROM cursor_Segment INTO @intTieline2, @intUpPort, @intDownPort
	
	WHILE (  @@FETCH_STATUS = 0 and @intSuccess = 0 )
	BEGIN
		----PRINT '	Found possible tieline for reuse: ' + CAST(@intTieline2 AS VARCHAR(6))
		-- Good so far we found a one hop solution
	
		-- Verify the signal types are in the same virtual level, otherwise we need to do a conversion
		-- and that's two hops
		SELECT @intCount = COUNT(*) from VirtualLevel
		-- Verify the Tieline supports these signal types. A Tieline may support multiple signal types
		-- so we can't include the ST in the WHERE clause we might miss out an a TL we can reuse.
		-- There will be 2 enties in the Tieline2PathDetail for a one hop, one for the InputTieline and the
		-- second for the OuptputTieline (which will be null for a one hop)
		SELECT @intCount = COUNT(*) from Tieline2PathDetail 
			WHERE Tieline2PathDetail_OutputTieline = @intTieline2 and
			  Tieline2PathDetail_SignalType = @intSrcSignalTypeIn and
			  Tieline2PathDetail_PhysLevel = @intSrcPhysLevelIn and
			  Tieline2PathDetail_SegmentIndex = 0;
			----PRINT '	Source : ' + CAST(@intCount AS VARCHAR(6))
	
		IF ( @intCount > 0 )
		BEGIN
			SELECT @intCount = COUNT(*) from Tieline2PathDetail 
			WHERE Tieline2PathDetail_InputTieline = @intTieline2 and
			  Tieline2PathDetail_SignalType = @intDestSignalTypeIn and
			  Tieline2PathDetail_PhysLevel = @intDestPhysLevelIn and
			  Tieline2PathDetail_SegmentIndex = 1;
		----PRINT '	Destination : ' + CAST(@intCount AS VARCHAR(6))
		END 
		
	
		IF ( @intCount > 0 )
		BEGIN
			-- Reserve this segment, reuse if possible.
			----PRINT '	Calling NV_ReserveTieline2Segement...'
			EXEC NV_ReserveTieline2Segment
						@intNvUserIn,
						@vchIPAddressIn,
						@vchLTCIn,
						@intTieline2,
						@vchSrcCtrlSysName,
						@intSrcPhysLevelIn, 
						@intSrcSignalTypeIn,
						@intSrcPortIn,
						@intDestPhysLevelIn, 
						@intDestSignalTypeIn,
						@intDestPortIn,
						@intDownPort,		-- Downstream
						@intUpPort,  -- Upstream
						@dtmCurrentTimestamp,
						@intHopCount,
						@intSuccess OUTPUT
				
			----PRINT '	NV_ReserveTieline2Segement returned intSuccess: ' + CAST(@intSuccess AS VARCHAR(6))

			IF ( @intSuccess = 1 )
			BEGIN
				----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(@intUpPort AS VARCHAR(6)) + ', ' + CAST(@intSrcPhysLevelIn AS VARCHAR(6))
										+ ', ' + CAST(@intDownPort AS VARCHAR(6)) + ', ' + CAST(@intDestPhysLevelIn AS VARCHAR(6))
				----PRINT ' Exiting  NV_ReserveExistingTieline2Path. vchTielines = ' + 	 @vchTielines  
			END
		END -- IF ( @intCount > 0 )
	
		IF ( @intSuccess = 0 )
		BEGIN
			FETCH NEXT FROM cursor_Segment INTO @intTieline2, @intUpPort, @intDownPort
		END

	END --WHILE (  @@FETCH_STATUS = 0 and @intSuccess = 0 )

	CLOSE cursor_Segment
	DEALLOCATE cursor_Segment 

END --WHILE (@intLoopCount < 3 and @intSuccess = 0)

IF ( @intSuccess = 1 )
	RETURN 0
ELSE
	RETURN 1

GO

/*
 * There aren't any one hop solutions so look for an existing path. We need to start with the destination information and
 * move backwards to the source.
 */
/*
set @vchTielines = ''
SET @intDone = 0
DECLARE curs_Destination CURSOR LOCAL FOR 
SELECT  Tieline2Reservation_Tieline2,
			Tieline2Reservation_SourcePhysLevel,
			Tieline2Reservation_SourceSignalType,	
			Tieline2Reservation_SourcePort,		
			Tieline2Reservation_DestinationPort,		
			Tieline2Reservation_UpstreamPort,
			Tieline2Reservation_DownstreamPort
	FROM Tieline2Reservation 
	WHERE 
			Tieline2Reservation_DestinationPhysLevel 	= @intDestPhysLevelIn and
			Tieline2Reservation_DestinationSignalType  	= @intDestSignalTypeIn 
			
OPEN curs_Destination

FETCH NEXT FROM curs_Destination
		INTO @intTieline2, @intSrcPhysLevel, @intSrcSignalType, @intSrcPort, @intDestPort,  @intUpPort, @intDownPort

-- Loop throught all the destinations that could be a fit
WHILE ( @@FETCH_STATUS = 0 and @intDone = 0 )
BEGIN

	SET @intSuccess = 0
	SET @intDone	= 0
	SET @intHopCount = 1
		
	-- Reserve this segment, reuse if possible.
	----PRINT '	Calling NV_ReserveTieline2Segement...'
	EXEC NV_ReserveTieline2Segment
				@intNvUserIn,
				@vchIPAddressIn,
				@vchLTCIn,
				@intTieline2,
				@intSrcPhysLevel, 
				@intSrcSignalType,
				@intSrcPort,			-- Don't use the source port passed in ... this is a muultihop and we haven't gotten to the src segment yet.
				@intDestPhysLevelIn, 
				@intDestSignalTypeIn,
				@intDestPortIn,			-- Used the destination port passed in
				@intUpPort,				-- Downstream
				@intDownPort,  			-- Upstream
				@dtmCurrentTimestamp,
				@intHopCount,
				@intSuccess OUTPUT
				
	----PRINT '	NV_ReserveTieline2Segement returned intSuccess: ' + CAST(@intSuccess AS VARCHAR(6))
		
	IF ( @intSuccess = 1 ) --Found and reserved destination segment
	BEGIN
		----PRINT '	We have successfully reserved (or reused) the destination segment'

		-- Start a new transaction. We may need to rollback inserts if we can't find a complete solution.
		IF ( @@trancount = 0 )
		BEGIN
			SELECT @intStartedTrans = 1
			begin tran p1
		END
		
		-- We've reserved this segment. Save the information to return to the caller and set us up for the next fetch.
		SET @vchTielines = CAST(@intTieline2 AS VARCHAR(6)) + ', ' + CAST(@intUpPort AS VARCHAR(6)) + ', ' + CAST(@intSrcPhysLevel AS VARCHAR(6))
									+ ', ' + CAST(@intDownPort AS VARCHAR(6)) + ', ' + CAST(@intDestPhysLevelIn AS VARCHAR(6))

		----PRINT 'Destination segment: ' + @vchTielines
	
		-- We've got an end solution, now back track looking for the start solution,
		-- get all the source detail ... use the destination information as we
		-- trace the segments back to the source.
		WHILE ( @intDone = 0 and @intSuccess = 1 )
		BEGIN
			SET @intTieline2 = -1
			SELECT
				@intTieline2 = Tieline2Reservation_Tieline2,
				@intSrcPhysLevel = Tieline2Reservation_SourcePhysLevel,
				@intSrcSignalType =	Tieline2Reservation_SourceSignalType,
				@intSrcPort = Tieline2Reservation_SourcePort,
				@intDestPhysLevel = Tieline2Reservation_DestinationPhysLevel,
				@intDestSignalType = Tieline2Reservation_DestinationSignalType,
				@intDestPort = Tieline2Reservation_DestinationPort,
				@intUpPort = Tieline2Reservation_UpstreamPort,
				@intDownPort = Tieline2Reservation_DownstreamPort 
			FROM Tieline2Reservation
			WHERE 
				Tieline2Reservation_DestinationPhysLevel = @intSrcPhysLevel and
				Tieline2Reservation_DestinationSignalType = @intSrcSignalType and
				Tieline2Reservation_UpstreamPort = @intSrcPort and
				Tieline2Reservation_DestinationPort = @intUpPort
					
			--We've got the next upstream segment 


			IF ( @intTieline2 <= 0 )
			BEGIN
				SET @intSuccess = 0
			END 
			ELSE
			BEGIN
			
				-- Is this the first upstream segment? Are we at the source?
				IF ( @intSrcPhysLevel = @intSrcPhysLevelIn and @intSrcSignalType = @intSrcSignalTypeIn   )
				BEGIN
					SELECT @intDone = 1

					--Use the source port passed in
					SET @intSrcPort = @intSrcPortIn
				END

				----PRINT 'ReserveExsitingTieline results from source fetch'
				exec NV_ConvertIntToString @intTieline2, @vchValue = @vchValue OUTPUT
				----PRINT 'Tieline2: ' + @vchValue
				exec NV_ConvertIntToString @intSrcPhysLevel, @vchValue = @vchValue OUTPUT			
				----PRINT 'SourcePhysLevel: ' + @vchValue
				exec NV_ConvertIntToString @intSrcSignalType, @vchValue = @vchValue OUTPUT
				----PRINT 'SourceSignalType: ' +  @vchValue
				exec NV_ConvertIntToString @intSrcPort, @vchValue = @vchValue OUTPUT
				----PRINT 'SourcePort: ' +   @vchValue
				exec NV_ConvertIntToString @intDestPhysLevel, @vchValue = @vchValue OUTPUT
				----PRINT 'intDestPhysLevel: ' + @vchValue
				exec NV_ConvertIntToString @intDestSignalType, @vchValue = @vchValue OUTPUT
				----PRINT 'intDestSignalType: ' +  @vchValue
				exec NV_ConvertIntToString @intDestPort, @vchValue = @vchValue OUTPUT
				----PRINT '@intDestPort: ' +   @vchValue
				exec NV_ConvertIntToString @intUpPort, @vchValue = @vchValue OUTPUT			
				----PRINT 'UpPort: ' +@vchValue
				exec NV_ConvertIntToString @intDownPort, @vchValue = @vchValue OUTPUT
				----PRINT '@intDownPort: ' +@vchValue


				-- Reserve this segment, reuse if possible.
				SET @intHopCount = @intHopCount + 1
				----PRINT '	Calling NV_ReserveTieline2Segement...'
				EXEC NV_ReserveTieline2Segment
							@intNvUserIn,
							@vchIPAddressIn,
							@vchLTCIn,
							@intTieline2,
							@intSrcPhysLevel, 
							@intSrcSignalType,
							@intSrcPort,
							@intDestPhysLevel, 
							@intDestSignalType,
							@intDestPort,
							@intUpPort,				-- Downstream
							@intDownPort,  			-- Upstream
							@dtmCurrentTimestamp,
							@intHopCount,
							@intSuccess OUTPUT
				
				----PRINT '	NV_ReserveTieline2Segement returned intSuccess: ' + CAST(@intSuccess AS VARCHAR(6))


				IF ( @intSuccess = 1 )
				BEGIN
				
					-- We've reserved this segment. Save the information to return to the caller and set us up for the next fetch.
					SET @vchTielines = @vchTielines + CAST(@intTieline2 AS VARCHAR(6)) + ', ' + CAST(@intUpPort AS VARCHAR(6)) + ', ' + CAST(@intSrcPhysLevel AS VARCHAR(6))
									+ ', ' + CAST(@intDownPort AS VARCHAR(6)) + ', ' + CAST(@intDestPhysLevel AS VARCHAR(6))

				
					----PRINT '		Source found ....'

					
				END --IF ( @intSuccess = 1 )

			END --IF ( @intTieline2 > 0 ) We've got a valid source tieline

		END	--WHILE ( @intDone = 0 )

		
		-- Didn't find a complete upstream solution ... keep looking
		IF ( @intDone = 0 or @intSuccess = 0 )
		BEGIN		
			----PRINT '		We did not find a complete upstream solution, so fetch another destination entry.'
			FETCH NEXT FROM curs_Destination
				INTO @intTieline2, @intSrcPhysLevel, @intSrcSignalType, @intSrcPort, @intDestPort,  @intUpPort, @intDownPort

			-- If we're inserted any segments then do a rollback.
			IF ( @intStartedTrans = 1 )
			BEGIN
				ROLLBACK TRAN p1
				SELECT @intStartedTrans = 0
			END
		END
		ELSE
		BEGIN
			
			----PRINT '		We did find a complete upstream solution: ' + @vchTielines

			IF ( @intStartedTrans = 1  )
				COMMIT TRAN p1	

			SET @intSuccess = 1
			SET @intDone = 1

		END

	END --IF ( @intSuccess = 1 ) Found and reserved destination segment

END --WHILE ( @@FETCH_STATUS = 0 and @intSuccess = 0 ) for curs_Destination

----PRINT ' Exiting  NV_ReserveExistingTieline2Path. vchTielines = ' + 	 @vchTielines

IF ( @intDone = 1 )
	RETURN 0
ELSE
	RETURN 1
*/
