

/*
 * Entry point for finding and reserving a Tleline2Path
 * 
 * Note: each call to master..xp_cmdshell returns an empty result set to the client
 */
 
use control

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

CREATE PROCEDURE NV_ReserveTieline2

@intNvUserIn			integer,
@vchIPAddressIn 		varchar(20),
@vchLTCIn				varchar(20),
@intSrcPhysLevelIn 		integer,
@intSrcSignalTypeIn		integer,
@intSrcPortIn			integer,		
@intDestPhysLevelIn		integer,
@intDestSignalTypeIn	integer,
@intDestPortIn			integer

AS

--PRINT ' Entering NV_ReserveTieline2 .... '

SET NOCOUNT ON;

declare @intCost integer
declare @intSuccess integer
declare @intStatus integer
declare @intTieline2Path integer
declare @vchTielines  varchar(200)
declare @intTCinWhereClause integer
SET @vchTielines = ''
SET @intSuccess = 0
SET @intStatus = 0


-- >>>>>>>>>>>>>>>  Lock access to keep tieline monitor from eating our brain while trying to execute reservation logic <<<<<<<<<<<<<<<<<<<
IF EXISTS(SELECT * FROM NvSysParam WHERE NvSysParam_Name = 'TLMON_LOCKOUT')
BEGIN
	UPDATE NvSysParam SET NvSysParam_Value = 'RES'  WHERE NvSysParam_Name = 'TLMON_LOCKOUT'
END
ELSE
BEGIN
	INSERT into NvSysParam values ('TLMON_LOCKOUT', 'RES', 'Exclude Tieline Monitor from operating on the database while we are making reservations')
END

/*
 * Look for an existing reservation to piggy back on? Or reuse?
 */

-- We're currently only doing this for one hops and the signal types have to be the same.
-- If a conversion is necessary then that would be two hops. Also, don't bother if we can't
-- find any tieline reservation where the source & destination physlevel's match.
IF ( @intSrcSignalTypeIn = @intDestSignalTypeIn and
     EXISTS (SELECT 1 FROM Tieline2Reservation
             WHERE Tieline2Reservation_SourcePhysLevel = @intSrcPhysLevelIn and 
			       Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevelIn ) )
BEGIN
	--PRINT ' Calling NV_ReserveExistingTieline2Path .... '

	-- Use the TC in the select statement with looking for a segment we can reuse. It's
	-- got the tick count encoded in it. This is only useful if the configuration uses
	-- TielineGrouping and TC isn't null or empty.
	SET @intTCinWhereClause = 0
	IF EXISTS(SELECT 1 FROM Tieline2Reservation WHERE Tieline2Reservation_LTC = @vchLTCIn )
		SET @intTCinWhereClause = 1

	EXECUTE NV_ReserveExistingTieline2Path 	
				@intNVUserIn,
				@vchIPAddressIn,
				@vchLTCIn,
				@intSrcPhysLevelIn ,
				@intSrcSignalTypeIn ,
				@intSrcPortIn,
				@intDestPhysLevelIn ,
				@intDestSignalTypeIn ,
				@intDestPortIn,
				@intTCinWhereClause,
				@vchTielines OUTPUT

	--PRINT 'NV_ReserveExistingTieline2Path returned: ' + @vchTielines
	
	/* 
	 * If we didn't find anything, try it without the tc information (which is actually tick count + source id, this
	 * only applies
	 */
	IF ( @intTCinWhereClause = 1 and @vchTielines = '' )
	BEGIN
		EXECUTE NV_ReserveExistingTieline2Path 	
				@intNVUserIn,
				@vchIPAddressIn,
				@vchLTCIn,
				@intSrcPhysLevelIn ,
				@intSrcSignalTypeIn ,
				@intSrcPortIn,
				@intDestPhysLevelIn ,
				@intDestSignalTypeIn ,
				@intDestPortIn,
				0,
				@vchTielines OUTPUT

		--PRINT 'NV_ReserveExistingTieline2Path 2nd call returned: ' + @vchTielines
	END
	
END

IF ( @vchTielines = '' )
BEGIN
	--PRINT 'Start looking for new path in Tieline2Path'

  -- Get a list of Tiline2Path's that satify the requirements
	DECLARE cursor_Tieline2Path  CURSOR  LOCAL FOR 
		SELECT Tieline2Path_ID, Tieline2Path_Cost
	 	FROM Tieline2Path
		WHERE
			Tieline2Path_SourcePhysLevel = @intSrcPhysLevelIn AND
			Tieline2Path_SourceSignalType = @intSrcSignalTypeIn AND
			Tieline2Path_DestinationPhysLevel = @intDestPhysLevelIn AND
			Tieline2Path_DestinationSignalType = @intDestSignalTypeIn
			order by Tieline2Path_Cost, Tieline2Path_ID
	
	OPEN cursor_Tieline2Path
	
	FETCH NEXT FROM cursor_Tieline2Path
	INTO @intTieline2Path, @intCost
	
	WHILE (  @@FETCH_STATUS = 0 and @intSuccess = 0 )
	BEGIN
		--PRINT 'Tieline2Path: ' + CAST(@intTieline2Path AS VARCHAR(6))
		--PRINT'Tieline Cost: '  + CAST(@intCost AS VARCHAR(6))
		
		-- Query Tieline2Reservations to see if this path is available
		-- is so then reserve it by entering it in the Tieline2Reservation
		-- table and return intSuccess = true. Other wise return false.
		--PRINT 'Call NV_ReserveTieline2Path with Tieline2Path: ' +  CAST(@intTieline2Path AS VARCHAR(6))
		
		-- Start a transaction if one hasn't been started yet
		--PRINT '@@trancount: ' + CAST(@@trancount AS VARCHAR(6))
		IF ( @@trancount = 0 )
		BEGIN
			--PRINT 'Beginning transaction before calling NV_ReserveTieline2Path'
			SET XACT_ABORT ON
			BEGIN tran p1
		END		
		
		SET @intStatus = 0
		EXEC @intSuccess = NV_ReserveTieline2Path @intNvUserIn, @vchIPAddressIn, @vchLTCIn, @intSrcPortIn, @intDestPortIn, 
				@intTieline2Path, @vchTielines OUTPUT, @intStatus OUTPUT

		-- We need to do a rollback or commit based on failure or success reserving a TL path
		-- Don't do a rollback if we we've deleted a tieline here, we might not be able to get a reservation at all, so don't delete
		-- a prefectly valid TL.
		IF ( @@trancount > 0 )
			BEGIN
			IF ( @intSuccess = 1 )
			BEGIN
				--PRINT 'Committing transaction in NV_ReservationTieline2 after processing path.'
				COMMIT tran p1
			END
			ELSE IF ( @intStatus != 99 )
			BEGIN 
				--PRINT 'Rolling back transaction in NV_ReservationTieline2 after processing path.'
				ROLLBACK tran p1
			END
		END
		
		--PRINT  'NV_ReserveTieline2Path intSuccess:  ' + CONVERT(VARCHAR, @intSuccess)
		--PRINT '                       @@trancount: ' + CAST(@@trancount AS VARCHAR(6))
		--PRINT ' NV_ReserveTieline2Path Tielines: ' + @vchTielines

		-- If we didn't find a path then keep on looking
		IF ( @intSuccess = 0 )
		BEGIN
			--PRINT 'NV_ReserveTieline2Path failed, try fetching next Tieline2Path'
			FETCH NEXT FROM cursor_Tieline2Path	INTO @intTieline2Path, @intCost

			--PRINT 'Status on fetch from Tieline2Path: ' + CONVERT(VARCHAR, @@FETCH_STATUS )
		END
	END
	
	CLOSE cursor_Tieline2Path
	DEALLOCATE cursor_Tieline2Path 
	
	--IF (  @@FETCH_STATUS = 0 and @intSuccess = 0  )
		--PRINT 'No rows found in Tieline2Path that matches selection criteria'
	
END --NV_GETExistingTielines didn't find anything


--PRINT 'Tielines found: ' + @vchTielines

-- We receive the list of Tieline2_ID, UpstreamPort, UpstreamPhysLevel, DownstreamPort and DownstreamPhysLevel as a string: '5, 99, 3, 101, 2'. 
-- If there are multiple path segment then they are separated by a comma
-- We need to return this as a ResultSet of integers, so do that ... using code I stole off the internet
IF ( @vchTielines != '' )
BEGIN

	DECLARE @intTieline2ID integer
	DECLARE @intUpstreamPort integer
	DECLARE @intUpstreamPhysLevel integer
	DECLARE @intDownstreamPort integer
	DECLARE @intDownstreamPhysLevel integer

	
	-- Return Tieline2_ID, UpstreamPort, UpstreamPhysLeve, DownstreamPort and DownstreamPhysLevel as a ResultSet using TABLE datatype 
	-- @vchTielines is the array we wish to parse
	-- @separator is the separator charactor such as a comma
	DECLARE @separator char(1)
	DECLARE @tabTielines TABLE  ( Tieline2ID integer, UpstreamPort integer,UpstreamPhysLevel integer, DownstreamPort integer, DownstreamPhysLevel integer )

	SET @separator = ','

	DECLARE @separator_position INT -- This is used to locate each separator character
	DECLARE @intValue integer -- this holds each ID or port value as it is returned

	-- For my loop to work I need an extra separator at the end. I always look to the
	-- left of the separator character for each tieline2ID value
   	 SET @vchTielines = @vchTielines + @separator

	DECLARE @index integer
	SET @index = 0
	
	-- Loop through the string searching for separtor characters
	WHILE PATINDEX('%' + @separator + '%', @vchTielines) <> 0 
	BEGIN
		SET @index = @index + 1
		
		-- patindex matches the a pattern against a string
		SET  @separator_position = PATINDEX('%' + @separator + '%',@vchTielines)
		SET  @intValue = LEFT(@vchTielines, @separator_position - 1)
		
		-- Now populate the TABLE variable that'll be returned to the caller
		-- @intValue holds the value of this element of the array

		IF ( @index = 1 )
		BEGIN
			SET @intTieline2ID =  @intValue
		END
		ELSE IF ( @index = 2 )
		BEGIN
			SET @intUpstreamPort =  @intValue
		END
		ELSE IF ( @index = 3 )
		BEGIN
			SET @intUpstreamPhysLevel =  @intValue
		END
		ELSE IF ( @index = 4 )
		BEGIN
			SET @intDownstreamPort =  @intValue
		END
		ELSE IF ( @index = 5 )
		BEGIN
			SET @intDownstreamPhysLevel =  @intValue
			INSERT into @tabTielines values ( @intTieline2ID, @intUpstreamPort, @intUpstreamPhysLevel, @intDownstreamPort, @intDownstreamPhysLevel )
			SET @index = 0 	-- reset to start
		END
	
		-- This replaces what we just processed with and empty string
		SET  @vchTielines = STUFF(@vchTielines, 1, @separator_position, '')
	END
	SELECT * FROM @tabTielines
	
	--PRINT ' Exiting NV_ReserveTieline2 .... '
	UPDATE NvSysParam SET NvSysParam_Value = 'FREE' WHERE NvSysParam_Name = 'TLMON_LOCKOUT'
	RETURN 0
END
ELSE
BEGIN
	--PRINT 'Just before exiting NV_ReserveTieline2 @@trancount: ' + CAST(@@trancount AS VARCHAR(6))
	--PRINT '                                        @intSuccess: ' + CAST(@intSuccess AS VARCHAR(6))
	IF ( @@trancount > 0 )
	BEGIN
		--PRINT ' Exiting NV_ReserveTieline2 ... do a rollback before leaving.'
		ROLLBACK tran p1
	END
	UPDATE NvSysParam SET NvSysParam_Value = 'FREE' WHERE NvSysParam_Name = 'TLMON_LOCKOUT'
	RETURN 1
END

