
/*
 * We're releasing an entire Tieline path (all segments)
 */

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

CREATE PROCEDURE NV_ReleaseTieline2Path
	@intNvUserIn integer,			-- The caller may not know this, so it could be -1
	@intSrcPhysLevelIn integer,		-- The caller may not know this, so it could be -1
	@intSrcSignalTypeIn integer,	--  The caller may not know this, so it could be -1
	@intSrcPortIn integer,			--  The caller may not know this, so it could be -1
	@intDestPhysLevelIn integer,
	@intUpPortIn integer,			-- The caller may not know this, so it could be -1
	@intDestPortIn integer

AS

SET NOCOUNT ON;

/* If the caller hasn't given us anything to work with then throw an exception */
IF ( @intDestPhysLevelIn < 1 )
BEGIN

	--PRINT 'Error: The DestPhysLevel can not be less then 1'
	RAISERROR ('Error: The DestPhysLevel can not be less then 1', 16, 1)
	RETURN 1
END

DECLARE @vchValue varchar(200)

/*
--PRINT 'Entering NV_ReleaseTieline2Path. Input parameters ...'
EXEC NV_ConvertIntToString @intNvUserIn, @vchValue = @vchValue OUTPUT
--PRINT '		@intNvUserIn: ' + @vchValue
EXEC NV_ConvertIntToString @intSrcPhysLevelIn, @vchValue = @vchValue OUTPUT
--PRINT 	'	@intSrcPhysLevelIn: ' + @vchValue
EXEC NV_ConvertIntToString @intSrcSignalTypeIn, @vchValue = @vchValue OUTPUT
--PRINT 	'	@intSrcSignalTypeIn: ' + @vchValue
EXEC NV_ConvertIntToString @intSrcPortIn, @vchValue = @vchValue OUTPUT
--PRINT 	'	@intSrcPortIn: ' + @vchValue
EXEC NV_ConvertIntToString @intDestPhysLevelIn, @vchValue = @vchValue OUTPUT
--PRINT '		@intDestPhysLevelIn: ' + @vchValue
EXEC NV_ConvertIntToString @intDestPortIn, @vchValue = @vchValue OUTPUT
--PRINT '		@intDestPortIn: ' + @vchValue
*/

declare @intDestDone integer
declare @intPathDone integer
declare @intSuccess integer
declare @intNvUser integer
declare @intTieline2 integer
declare @intSrcPhysLevel integer
declare @intSrcSignalType integer
declare @intSrcPort integer
declare @intDestPhysLevel integer
declare @intDestSignalType integer
declare @intDestPort integer
declare @intUpPort integer
declare @intDownPort integer

-- Keep unique keys of Tieline2Reservation table in a local TABLE variable. If we get a complete solution
-- (Source to Destination) we'll loop through this table and delete all segments, so we don't need to 
-- worry about doing a partial release and rollback.
declare @tabDeleteData TABLE ( Tieline2ID integer, NvUser integer, DestPhysLevel integer, UpPort integer, DestPort integer )

declare @intStartedTrans integer	-- set to 1 if we start a transaction
SET @intStartedTrans = 0

SET @intDestDone = 0
SET @intPathDone = 0
SET @intSuccess = 0		--We're successful.


-- We don't have all the data for the Reservation table's unique key, so create a cursor with the information
-- that we do have. It we can't finish releasing the entire Tieline then rollback and try again.

-- Start with the destination and go upstream.

-- Cursor looping through all rows that satisfy Destination data

DECLARE @cursorRelease CURSOR

IF ( @intNvUserIn > 0 and @intUpPortIn > 0 )
BEGIN
	SET @cursorRelease = CURSOR LOCAL FOR 
		SELECT
			Tieline2Reservation_Tieline2,
			Tieline2Reservation_User,
			Tieline2Reservation_SourcePhysLevel,
			Tieline2Reservation_SourceSignalType,
			Tieline2Reservation_SourcePort,
			Tieline2Reservation_UpstreamPort,
			Tieline2Reservation_DownstreamPort 
		FROM Tieline2Reservation
		WHERE 
			Tieline2Reservation_User = @intNvUserIn and 
			Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevelIn and
			Tieline2Reservation_UpstreamPort = @intUpPortIn and 
			Tieline2Reservation_DestinationPort = @intDestPortIn
END
ELSE IF ( @intNvUserIn > 0 )
BEGIN
	SET @cursorRelease = CURSOR LOCAL FOR 
		SELECT
			Tieline2Reservation_Tieline2,
			Tieline2Reservation_User,
			Tieline2Reservation_SourcePhysLevel,
			Tieline2Reservation_SourceSignalType,
			Tieline2Reservation_SourcePort,
			Tieline2Reservation_UpstreamPort,
			Tieline2Reservation_DownstreamPort 
		FROM Tieline2Reservation
		WHERE 
			Tieline2Reservation_User = @intNvUserIn and 
			Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevelIn and
			Tieline2Reservation_DestinationPort = @intDestPortIn
END
ELSE IF ( @intUpPortIn > 0 )
BEGIN
	SET @cursorRelease = CURSOR LOCAL FOR 
		SELECT
			Tieline2Reservation_Tieline2,
			Tieline2Reservation_User,
			Tieline2Reservation_SourcePhysLevel,
			Tieline2Reservation_SourceSignalType,
			Tieline2Reservation_SourcePort,
			Tieline2Reservation_UpstreamPort,
			Tieline2Reservation_DownstreamPort 
		FROM Tieline2Reservation
		WHERE 
			Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevelIn and
			Tieline2Reservation_UpstreamPort = @intUpPortIn and 
			Tieline2Reservation_DestinationPort = @intDestPortIn
END
ELSE
BEGIN
	SET @cursorRelease = CURSOR LOCAL FOR 
		SELECT
			Tieline2Reservation_Tieline2,
			Tieline2Reservation_User,
			Tieline2Reservation_SourcePhysLevel,
			Tieline2Reservation_SourceSignalType,
			Tieline2Reservation_SourcePort,
			Tieline2Reservation_UpstreamPort,
			Tieline2Reservation_DownstreamPort 
		FROM Tieline2Reservation
		WHERE 
			Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevelIn and
			Tieline2Reservation_DestinationPort = @intDestPortIn
END 
 

OPEN @cursorRelease

FETCH @cursorRelease INTO @intTieline2, @intNvUser, @intSrcPhysLevel, @intSrcSignalType, @intSrcPort, @intUpPort, @intDownPort

IF (  @@fetch_status < 0   )
BEGIN
	--PRINT '		Big problem can not find match based on procedure input parameters'
	
	CLOSE @cursorRelease
	DEALLOCATE @cursorRelease 

	--PRINT 'Exiting NV_ReleaseTieline2Path.'
	RETURN 1
END

--Try different Tieline Paths, based on destination data
WHILE ( @@fetch_status >= 0 and @intDestDone = 0 and @intSuccess = 0 )
BEGIN

	-- Delete any previous entries ... we're either going through this loop for the 1st time or else
	-- we're trying a new destination entry.
	DELETE FROM @tabDeleteData
	
	--PRINT '		Found tieline: ' +  + CONVERT(VARCHAR, @intTieline2) + ' store segment information.'
	INSERT into @tabDeleteData values ( @intTieline2, @intNvUser, @intDestPhysLevelIn, @intUpPort, @intDestPortIn )

	-- Are we done?
	IF ( @intSrcPhysLevelIn > 0 or @intSrcSignalTypeIn > 0 or @intSrcPortIn > 0 )
	BEGIN
		DECLARE @intFinished integer
		SET @intFinished = 1

		IF ( @intSrcPhysLevelIn > 0 and @intSrcPhysLevel != @intSrcPhysLevelIn)
			SET @intFinished = 0

		IF ( @intSrcSignalTypeIn > 0 and @intSrcSignalType != @intSrcSignalTypeIn)
			SET @intFinished = 0

		IF ( @intSrcPortIn > 0 and @intSrcPort != @intSrcPortIn)
			SET @intFinished = 0

		IF ( @intFinished = 1 )
			 	SET @intSuccess = 1
	END

	-- Find all the segments from the destination to the source
	WHILE ( @intPathDone = 0 and @intSuccess = 0 )
	BEGIN
		-- Set up for selecting the next upstream segment
		SET @intDestPhysLevel = @intSrcPhysLevel
		SET @intDestSignalType = @intSrcSignalType
		SET @intDestPort = @intUpPort
		SET @intUpPort = @intSrcPort
		
		SET @intTieline2 = -1
		SELECT
			@intTieline2 = Tieline2Reservation_Tieline2,
			@intSrcPhysLevel = Tieline2Reservation_SourcePhysLevel,
			@intSrcSignalType =	Tieline2Reservation_SourceSignalType,
			@intSrcPort = Tieline2Reservation_SourcePort,
			@intUpPort = Tieline2Reservation_UpstreamPort,
			@intDownPort = Tieline2Reservation_DownstreamPort 
		FROM Tieline2Reservation
		WHERE
			Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevel and
			Tieline2Reservation_DestinationSignalType = @intDestSignalType and
			Tieline2Reservation_UpstreamPort = @intUpPort and
			Tieline2Reservation_DestinationPort = @intDestPort and
			Tieline2Reservation_User = @intNvUser
			
		IF ( @intTieline2 > 0 )
		BEGIN
			--PRINT '		Found tieline: ' +  + CONVERT(VARCHAR, @intTieline2) + ' store segment information.'
			INSERT into @tabDeleteData values ( @intTieline2, @intNvUser, @intDestPhysLevel, @intUpPort, @intDestPort )
		
			-- Are we done? If we received the source information as input parameters then check to see if we found 
			-- a source entry that matches the source intput parameters?
			IF ( @intSrcPhysLevelIn > 0 or @intSrcSignalTypeIn > 0 or @intSrcPortIn > 0 )
			BEGIN
				SET @intFinished = 1

					IF ( @intSrcPhysLevelIn > 0 and @intSrcPhysLevel != @intSrcPhysLevelIn)
						SET @intFinished = 0

			    	 IF ( @intSrcSignalTypeIn > 0 and @intSrcSignalType != @intSrcSignalTypeIn)
			      		SET @intFinished = 0

					IF ( @intSrcPortIn > 0 and @intSrcPort != @intSrcPortIn)
			      		SET @intFinished = 0

					IF ( @intFinished = 1 )
			 			SET @intSuccess = 1
			 END
			
			--Find the next upstream segment
		END
		ELSE
		BEGIN
			--PRINT '		Finished. Run out of upstream segments for this destination match'
			
			-- Are we done? If we received the source information as input parameters then check to see if we found 
			-- a source entry that matches the source intput parameters? But, if there aren't any rows, then obviously
			-- we can't be finished.
			IF ( SELECT count(*) from @tabDeleteData ) > 0 
			BEGIN
				IF ( @intSrcPhysLevelIn > 0 or @intSrcSignalTypeIn > 0 or @intSrcPortIn > 0 )
				BEGIN
					SET @intFinished = 1

					IF ( @intSrcPhysLevelIn > 0 and @intSrcPhysLevel != @intSrcPhysLevelIn)
						SET @intFinished = 0

			    	 IF ( @intSrcSignalTypeIn > 0 and @intSrcSignalType != @intSrcSignalTypeIn)
			      		SET @intFinished = 0

					IF ( @intSrcPortIn > 0 and @intSrcPort != @intSrcPortIn)
			      		SET @intFinished = 0

					IF ( @intFinished = 1 )
			 			SET @intSuccess = 1
			 	END
				ELSE
				BEGIN
					--PRINT 'Source is undefined. assumed we are finished'
					SET @intSuccess = 1
				END
			END
				
			-- No more upstream segments, we're done looking
			SET @intPathDone = 1
		END	

	END	--WHILE ( @intPathDone = 0 )	
				
	--Fetch next destination match
	FETCH @cursorRelease INTO @intTieline2, @intNVUser, @intSrcPhysLevel, @intSrcSignalType, @intSrcPort, @intUpPort, @intDownPort
		
END --WHILE ( @intDestDone = 0 )

CLOSE @cursorRelease
DEALLOCATE @cursorRelease


IF ( @intSuccess = 1 )
BEGIN
	--PRINT 'Success ... start deleting segments.'

	-- Has a tranaction been started?
	DECLARE @intStartTrans integer
	SET @intStartTrans = 0
	
	DECLARE cursor_Delete CURSOR LOCAL FOR
		SELECT *
		FROM @tabDeleteData
		
	OPEN cursor_Delete

 	FETCH cursor_Delete INTO @intTieline2, @intNvUser, @intDestPhysLevel, @intUpPort, @intDestPort
 	
 	WHILE ( @intSuccess = 1 and @@fetch_status >= 0 )
 	BEGIN
 		IF ( @@trancount = 0 )
 		BEGIN		
 			BEGIN tran t1
 			SET @intStartTrans = 1	--set flag indicating we've started a transaction
			--PRINT 'Started transation.'
 		END
 		
 		--PRINT 'Deleting segment: Tieline2_ID: ' +  CAST(@intTieline2 AS VARCHAR(6))
 		--PRINT '                         User: ' + CAST(@intNvUser AS VARCHAR(6))
 		--PRINT '         DestinationPhysLevel: ' + CAST(@intDestPhysLevel AS VARCHAR(6))
		--PRINT '                 UpstreamPort: ' + CAST(@intUpPort AS VARCHAR(6))
 		--PRINT '              DestinationPort: ' + CAST(@intDestPort AS VARCHAR(6))
 		
 		DELETE Tieline2Reservation 
 		WHERE 
 			Tieline2Reservation_Tieline2 = @intTieline2 and
 			Tieline2Reservation_User = @intNvUser and
 			Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevel and
			Tieline2Reservation_UpstreamPort = @intUpPort and
 			Tieline2Reservation_DestinationPort = @intDestPort
 		
 		--If there's an error then do a rollback
 		IF ( @@error != 0 )
 		BEGIN
 			SET @intSuccess = 0
 			--PRINT 'Error on delete.'
 		END	
 		
 		--Set up for next delete
 		FETCH cursor_Delete INTO @intTieline2, @intNvUser, @intDestPhysLevel, @intUpPort, @intDestPort
 		
 	END
 	
 	IF ( @intStartTrans = 1 )
 	BEGIN
 		IF ( @intSuccess = 1 )
 		BEGIN
	 		COMMIT tran t1
	 		--PRINT 'Transaction committed.'
	 	END
	 	ELSE
	 	BEGIN
	 		ROLLBACK tran t1
	 		--PRINT 'Transaction rolledback.'
	 	END
 	END
 
 	
END

----PRINT 'Exiting NV_ReleaseTieline2Path.'

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

