
use control

/*
 * Is a Tieline being shared? Return the Tieline2_ID if we can.
 * Values returned through the OUPUT: 'N' not shared at all
 *								      'B' match on both ends down to the port level
 *								      'S' source port match, but the destination ports has different values
 *								      'D' destination port match, but the source ports are different
 */

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

CREATE PROCEDURE NV_IsTielineSegmentShared 	
				@intNvUserIn			integer,
				@intTieline2In			integer,
				@vchLTCIn				varchar(20),
				@vchControlSystemNameIn varchar(24),
				@intSrcPhysLevelIn 	 	integer,
				@intSrcSignalTypeIn 	integer ,
				@intSrcPortIn			integer,
				@intDestPhysLevelIn 	integer ,
				@intDestSignalTypeIn	integer ,
				@intDestPortIn			integer,
				@intNvUserShareCountOut	integer = 0 OUTPUT,			--How many of these entries are used by NvUser? 
				@intShareCountOut		integer = 0 OUTPUT,			--How many entries matched?
				@vchShareTypeOut  		varchar(1) = 'N'  OUTPUT			--What type of share do we have?

AS

--PRINT ''
--PRINT 'Entering NV_IsTielineSegmentShared'

SET NOCOUNT ON;

DECLARE @vchValue varchar(200)

DECLARE @intDone integer
SET @intDone = 0

DECLARE @intSuccess integer
SET @intSuccess = 0

/*
 * See if we can use the blank reservation that's created by NV_ReserveTieline2GroupTrigger after the 1st
 * reservation is made. Example: If Tielines are created for Video, AES 1 and AES 2 when a request comes
 * in for Video a reservation is also made for AES 1 and AES 2, but the SourcePl, Source ST, Source Port,
 * DestPL, DestST, DestPort, DownstreamPort and Upstream Port are all 0, resuse this entry in the reservation
 * table and pug in the detailed information.
 */
DECLARE @intCount integer
SET @intCount = 0

SELECT @intCount = COUNT(*) from Tieline2GroupBinding
	where Tieline2GroupBinding_Tieline2 = @intTieline2In
	
IF ( @intCount = 1 )
BEGIN
	--Look for the placeholder entry in the reservation table
	SELECT @intCount = COUNT(*) FROM Tieline2Reservation
	WHERE 
		Tieline2Reservation_Tieline2 = @intTieline2In and
		Tieline2Reservation_ControlSystem_Name like @vchControlSystemNameIn and
		Tieline2Reservation_User = @intNvUserIn and
		Tieline2Reservation_SourcePort = 0 and
		Tieline2Reservation_DestinationPort = 0 and
		Tieline2Reservation_LTC = @vchLTCIn 
	
	IF ( @intCount = 1 )
	BEGIN
		SET @vchShareTypeOut = 'G'
		SET @intShareCountOut = 1
		SET @intNvUserShareCountOut = 1
		--PRINT 'Blank tieline reservation created by NV_ReservationTieline2GroupTrigger found which can be reused'
		--PRINT 'Exiting NV_IsTielineSegmentShared'
		--PRINT ''
		RETURN 0
	END 
END
	
/*
 * How many users are using this Tieline?
 */	

-- 8/26/08: forget about matching signal types, it shouldn't matter per Kieran

--How many are using this tieline?
SELECT @intShareCountOut = COUNT(*) FROM Tieline2Reservation
WHERE 
	Tieline2Reservation_Tieline2 = @intTieline2In and
	Tieline2Reservation_ControlSystem_Name like @vchControlSystemNameIn
	-- and Tieline2Reservation_SourcePhysLevel = @intSrcPhysLevelIn
	-- and Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevelIn 

-- Nothing, the default OUTPUT values are fine.
IF ( @intShareCountOut = 0 )
BEGIN
	--PRINT 'Tieline not in use'
	--PRINT 'Exiting NV_IsTielineSegmentShared'
	--PRINT ''
	RETURN 0
END
ELSE
BEGIN
	-- How many (if any) are intNvUserIns? Set the value here even though it may get reset further down. This is
	-- the case where the @intNvUserIn is using this segment, but with a different source and destination
	SELECT @intNvUserShareCountOut = COUNT(*) FROM Tieline2Reservation
	WHERE 
		Tieline2Reservation_Tieline2 = @intTieline2In and
		Tieline2Reservation_ControlSystem_Name like @vchControlSystemNameIn and
		Tieline2Reservation_User = @intNvUserIn and
		Tieline2Reservation_SourcePhysLevel = @intSrcPhysLevelIn and
		Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevelIn 
END

DECLARE @intNvUser integer

/*
 * Exact match: source and destination ports match
 */
SET @intNvUser = 0

DECLARE cursor_Reservation CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
	SELECT Tieline2Reservation_User  from Tieline2Reservation
	where 
		Tieline2Reservation_Tieline2 = @intTieline2In and
		Tieline2Reservation_ControlSystem_Name like @vchControlSystemNameIn and
		Tieline2Reservation_SourcePhysLevel = @intSrcPhysLevelIn and
		Tieline2Reservation_SourcePort = @intSrcPortIn and
		Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevelIn and
		Tieline2Reservation_DestinationPort = @intDestPortIn

OPEN cursor_Reservation

FETCH cursor_Reservation INTO @intNvUser

-- Keep looping until no more rows, we need to get a count of how many are used
-- by intNVUser
IF ( @@FETCH_STATUS = 0 )
BEGIN
	SET @intNvUserShareCountOut = 0
	WHILE ( @@FETCH_STATUS = 0 and @@error = 0 and @intDone = 0 )
	BEGIN

		SET @vchShareTypeOut = 'B'
		SET @intSuccess = 1
	
		IF ( @intNvUser = @intNvUserIn )
		BEGIN
			SET @intNvUserShareCountOut = @intNvUserShareCountOut + 1
		END
		
		FETCH cursor_Reservation INTO @intNvUser
	END
END

CLOSE cursor_Reservation
DEALLOCATE cursor_Reservation 

-- Are we done?
IF ( @intSuccess = 1 )
BEGIN
	--PRINT 'Exiting NV_IsTielineSegmentShared'
	--PRINT ''
	RETURN 0
END

	
/*
 * Only the sources match
 */
SET @intNvUser = 0

DECLARE cursor_Reservation CURSOR LOCAL FOR 
	SELECT Tieline2Reservation_User from Tieline2Reservation
	where 
	Tieline2Reservation_Tieline2 = @intTieline2In and
	Tieline2Reservation_ControlSystem_Name like @vchControlSystemNameIn and
	Tieline2Reservation_SourcePhysLevel = @intSrcPhysLevelIn and
	Tieline2Reservation_SourcePort = @intSrcPortIn and NOT (
	Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevelIn and
	Tieline2Reservation_DestinationPort = @intDestPortIn )

OPEN cursor_Reservation

FETCH cursor_Reservation INTO @intNvUser

-- Keep looping until no more rows, we need to get a count of how many are used
-- by intNVUser
IF ( @@FETCH_STATUS = 0 )
BEGIN
	SET @intNvUserShareCountOut = 0
	WHILE ( @@FETCH_STATUS = 0 and @@error = 0 and @intDone = 0 )
	BEGIN

		SET @vchShareTypeOut = 'S'
		SET @intSuccess = 1
	
		IF ( @intNvUser = @intNvUserIn )
		BEGIN
			SET @intNvUserShareCountOut = @intNvUserShareCountOut + 1
		END
		
		FETCH cursor_Reservation INTO @intNvUser
	END
END

CLOSE cursor_Reservation
DEALLOCATE cursor_Reservation 

-- Are we done?
IF ( @intSuccess = 1 )
BEGIN
	--PRINT 'Exiting NV_IsTielineSegmentShared'
	--PRINT ''
	RETURN 0
END

/*
 * Only the destinations match
 */
SET @intNvUser = 0

DECLARE cursor_Reservation CURSOR LOCAL FOR 
	SELECT Tieline2Reservation_User from Tieline2Reservation
	where 
	
		NOT (Tieline2Reservation_SourcePhysLevel = @intSrcPhysLevelIn and
		Tieline2Reservation_SourcePort = @intSrcPortIn ) and
		Tieline2Reservation_Tieline2 = @intTieline2In and
		Tieline2Reservation_ControlSystem_Name like @vchControlSystemNameIn and
		Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevelIn and
		Tieline2Reservation_DestinationPort = @intDestPortIn
		
OPEN cursor_Reservation

FETCH cursor_Reservation INTO @intNvUser

-- Keep looping until no more rows, we need to get a count of how many are used
-- by intNVUser
IF ( @@FETCH_STATUS = 0 )
BEGIN
	SET @intNvUserShareCountOut = 0
	WHILE ( @@FETCH_STATUS = 0 and @@error = 0 and @intDone = 0 )
	BEGIN

		SET @vchShareTypeOut = 'D'
		SET @intSuccess = 1
	
		IF ( @intNvUser = @intNvUserIn )
		BEGIN
			SET @intNvUserShareCountOut = @intNvUserShareCountOut + 1
		END
		
		FETCH cursor_Reservation INTO @intNvUser
	END
END

CLOSE cursor_Reservation
DEALLOCATE cursor_Reservation 

--PRINT 'Exiting NV_IsTielineSegmentShared'
--PRINT ''

RETURN 0
		
