use control

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

CREATE PROCEDURE NV_ReserveTieline2Segment
				@intNvUserIn		integer,
				@vchIPAddressIn 	varchar(20),
				@vchLTCIn 			varchar(20),
				@intTieline2In		integer,
				@vchSrcCtrlSyNameIn varchar(24),
				@intSrcPhysLevelIn 	 integer,
				@intSrcSignalTypeIn integer ,
				@intSrcPortIn		integer,
				@intDestPhysLevelIn integer ,
				@intDestSignalTypeIn integer ,
				@intDestPortIn		integer,
				@intDownPortIn		integer,
				@intUpPortIn		integer,
				@dtmCurrentTimestampIn datetime,
				@intHopCountIn		integer,
				@intSuccess integer = 0 OUTPUT

AS

SET NOCOUNT ON;
--PRINT 'Entering NV_ReserveTieline2Segment'

--EXEC master..xp_cmdshell 'echo Entering NV_ReserveTieline2Segment >> c:\SQLDebugFile'

-- See if this Tieline is excluded for user by the physical level and port number
IF EXISTS (SELECT * FROM TIELINE2EXCLUSION 
    			 WHERE (Tieline2Exclusion_Tieline2 = @intTieline2In AND				-- Check for source exclusion
            			Tieline2Exclusion_PhysLevel = @intSrcPhysLevelIn AND
            			Tieline2Exclusion_Port = @intSrcPortIn AND
            			Tieline2Exclusion_Direction = 1 ) OR
            			(Tieline2Exclusion_Tieline2 = @intTieline2In and				-- Check for destination exclusion
           				 Tieline2Exclusion_PhysLevel = @intDestPhysLevelIn and
           				 Tieline2Exclusion_Port = @intDestPortIn AND
            				Tieline2Exclusion_Direction = 2 ) 
            )
BEGIN
	--PRINT '    Tieline: ' + CAST(@intTieline2In AS VARCHAR(6)) + ' is excluded from use'
	--PRINT 'Exiting NV_ReserveTieline2Segment'
	RETURN 1
END 



-- See if this is on a remote system. If the control system id != 1 then it is remote 
DECLARE @intControlSystemID 		integer
DECLARE @intControlSystem_Tieline2 	integer
DECLARE @vchControlSystemIPAddress 	varchar(25)
SET @intControlSystemID = -1

SELECT  @intControlSystemID = Tieline2_ControlSystem,
		@intControlSystem_Tieline2 = Tieline2_ControlSystem_Tieline2,
		@vchControlSystemIPAddress = ControlSystem_IP
	FROM Tieline2, ControlSystem
	WHERE
		Tieline2_ID = @intTieline2In and
		Tieline2_ControlSystem = ControlSystem_ID

DECLARE @intNvUserShare integer		--Is the current user using this segment
DECLARE @intShareCount integer		-- How many user's are sharing it?
DECLARE @vchShareType varchar(1)	-- What's the share type? 'B' = source and dest ports are the same, 'S' = just the source, 'D' = just the dest, 'N' = neither

SET @intNvUserShare = 0
SET @intShareCount = 0
SET @vchShareType = 'N'

-- Reserve this segment, reuse if possible. Get the state of this current segment ...
--PRINT '	Calling NV_IsTielineSegmentShared...'
--EXEC master..xp_cmdshell 'echo ECalling NV_IsTielineSegmentShared >> c:\SQLDebugFile'

EXEC NV_IsTielineSegmentShared
		@intNvUserIn,
		@intTieline2In,
		@vchLTCIn,
		@vchSrcCtrlSyNameIn,
		@intSrcPhysLevelIn, 
		@intSrcSignalTypeIn,
		@intSrcPortIn,
		@intDestPhysLevelIn, 
		@intDestSignalTypeIn,
		@intDestPortIn,
		@intNvUserShare OUTPUT,			--Is NvUserIn one of the ones that shares this Tieline, values are either 0 or 1 
		@intShareCount OUTPUT,			-- How many users share this tieline, if there's one to consider this value is greate than 0
		@vchShareType  OUTPUT	
		
--PRINT '	NV_IsTielineSegmentShared returned ' +
--									' intNvUserShare: ' + CAST(@intNvUserShare AS VARCHAR(6)) + 
--									', intShareCount: ' + CAST(@intShareCount AS VARCHAR(6)) + 
--									', vchShareType:  ' + @vchShareType

DECLARE @intInsertIt integer	-- Insert into Reservation table.
DECLARE @intUseIt integer	--No change, just return the informaiton needed from an existing Tieline2Reservation entry
DECLARE @intUpdateSource integer
DECLARE @intDropAndReserve integer
DECLARE @intUpdateBlankTielineGroup integer
DECLARE @intStatus integer

SET @intStatus = -1		-- undefined
SET @intInsertIt = 0	-- New entry in the Reservation table															

/*
 * Figure out what to do
 */
IF ( @vchShareType = 'G ')
BEGIN
	SET @intUpdateBlankTielineGroup = 1
END 
ELSE IF ( @intShareCount = 0 )
BEGIN
	SET @intInsertIt = 1	--Not being used at all, go ahead and insert it into the reservations table
	--PRINT 'Segment is unused.'
--	EXEC master..xp_cmdshell 'echo Segment is unused >> c:\SQLDebugFile'
END
ELSE
BEGIN
	SET @intUseIt = 0	-- Use the same Tieline2Reservation entry
	SET @intUpdateSource = 0 -- Update the source port number
	SET @intDropAndReserve = 0 -- The source has change, the destination is the same, but the Tieline is shared

	IF ( @intNvUserShare >= 1 )	-- Same user
	BEGIN 
		--PRINT 'Same user.'
		--EXEC master..xp_cmdshell 'echo Same user. >> c:\SQLDebugFile'
		IF ( @vchShareType = 'B'  ) 	--Both Source port and destination port as the same as requested
		BEGIN
			SET @intUseIt = 1
			IF ( @intShareCount > 1 )
			BEGIN
				--PRINT 'Source and destination ports match. Tieline is shared.'
				SET @intStatus = 77
			END 
			--ELSE
			--BEGIN
				--PRINT 'Source and destination ports match. Tieline is not shared.'
			--END 
		END
		ELSE IF ( @vchShareType = 'S' ) -- Source port is the same, but destination is different
		BEGIN
			SET @intInsertIt = 1
			--PRINT 'Source ports match.'
		END
		ELSE IF ( @vchShareType = 'D' ) -- Destination port is the same, but the source is different
		BEGIN
		
			IF ( @intShareCount = 1 )   -- Just NvUserIn
			BEGIN
				SET @intUpdateSource = 1 --We just need to update the Source port
				--PRINT 'Destination ports match, only the current user is using it.'
			END
			ELSE
			BEGIN
				--We need to delete this user's entry and reserve a new tieline
				SET @intDropAndReserve = 1
				--PRINT 'Destination ports match, other user has it (or shared it).'
			END
		END
	END
	ELSE
	BEGIN
		--Different user
		--PRINT 'Different user is using it.'
		IF ( @vchShareType = 'B' or @vchShareType = 'S' )
		BEGIN
			SET @intInsertIt = 1
			--PRINT 'Different user is using it, either both ports are the same or else the source port is the same'
		END
		--ELSE
		--BEGIN
			--PRINT 'Different user is using it, either both ports are different or else the destination port is the same'
		--END
	END
END

/*
 * Do what needs to be done
 */
IF ( @intUpdateBlankTielineGroup = 1 )
BEGIN
	UPDATE Tieline2Reservation SET
		Tieline2Reservation_SourceSignalType = @intSrcSignalTypeIn,
		Tieline2Reservation_SourcePort = @intSrcPortIn,
		Tieline2Reservation_DestinationSignalType = @intDestSignalTypeIn,
		Tieline2Reservation_DestinationPort = @intDestPortIn,
		Tieline2Reservation_UpstreamPort = @intUpPortIn,
		Tieline2Reservation_DownstreamPort = @intDownPortIn
	WHERE 
		Tieline2Reservation_Tieline2 = @intTieline2In and
		Tieline2Reservation_ControlSystem_Name like @vchSrcCtrlSyNameIn and
		Tieline2Reservation_User = @intNvUserIn and
		Tieline2Reservation_SourcePhysLevel = @intSrcPhysLevelIn and
		Tieline2Reservation_DestinationPhysLevel = @intDestPhysLevelIn and
		Tieline2Reservation_DestinationPort = 0 and
		Tieline2Reservation_SourcePort = 0
		
		SET @intSuccess = 1	--We're done
END 
ELSE IF ( @intUseIt = 1 )
BEGIN
	--PRINT 'intUserIT = 1 ... we are done'
	SET @intSuccess = 1
END
ELSE IF ( @intInsertIt = 1 )
BEGIN		
	--PRINT 'intInsertIt = 1 ... do insert'
	--EXEC master..xp_cmdshell 'echo intInsertIt = 1 ... do insert >> c:\SQLDebugFile'
	
	SET @intStatus = 0

	-- If this tieline is owned by a remote system then reserve it on that system
	-- we also want to reserve it on this system as well, so we have a complete path
	-- in the reservation table.
	IF ( @intControlSystemID > 1 )
	BEGIN
		--PRINT 'Also reserve segment on remote system'
		--EXEC master..xp_cmdshell 'echo Also reserve segment on remote system >> c:\SQLDebugFile'

		DECLARE @vchRemoteServerIPAddress varchar(24)
		SET @vchRemoteServerIPAddress = (SELECT ControlSystem_IP FROM ControlSystem where ControlSystem_ID = @intControlSystemID )
		
		DECLARE @vchRemoteServerName varchar(24)
		SET @vchRemoteServerName = (SELECT ControlSystem_Name FROM ControlSystem where ControlSystem_ID = @intControlSystemID )
		
		DECLARE @vchDynamicSQLStmt nvarchar(4000)
		DECLARE @vchParamDefinition nvarchar(4000)
		
		-- Use for testing only, db name would always be 'control'
		DECLARE @vchDBName varchar(30)
		SET @vchDBName = 'control'
		

		SET @vchDynamicSQLStmt = '[' +  @vchRemoteServerIPAddress + '].[' + @vchDBName  + '].[dbo].NV_ReserveTieline2Segment ' +
		                         CAST(@intNvUserShare AS VARCHAR(6)) + ', ' +
		                       	 '''' + @vchIPAddressIn + ''',' +
		                         '''' + @vchLTCIn  + ''',' +
		                         CAST(@intControlSystem_Tieline2 AS VARCHAR(6)) + ', ' +
		                         '''' + @vchRemoteServerName + ''',' +
		                         CAST(@intSrcPhysLevelIn AS VARCHAR(6)) + ', ' +
		                         CAST(@intSrcSignalTypeIn AS VARCHAR(6)) + ', ' +
								 CAST(@intSrcPortIn AS VARCHAR(6)) + ', ' +
								 CAST(@intDestPhysLevelIn AS VARCHAR(6)) + ', ' +
								 CAST(@intDestSignalTypeIn AS VARCHAR(6)) + ', ' +
								 CAST(@intDestPortIn AS VARCHAR(6)) + ', ' +
								 CAST(@intDownPortIn AS VARCHAR(6)) + ', ' +
								 CAST(@intUpPortIn AS VARCHAR(6)) + ', ' +
								 '''' + CAST(@dtmCurrentTimestampIn AS VARCHAR(20)) + ''', ' +
								 CAST(@intHopCountIn AS VARCHAR(6))
						
		--PRINT 'Executing: ' + @vchDynamicSQLStmt
		EXEC ( @vchDynamicSQLStmt )

		--PRINT 'Status returned from remote stored procedure execution: ' + CAST( @intStatus AS VARCHAR(6) )           
	END

	/* Reserve it on the local machine as well */
	IF ( @intStatus = 0  )
	BEGIN

		--EXEC master..xp_cmdshell 'echo just before insert >> c:\SQLDebugFile'
		INSERT into Tieline2Reservation ( 
				Tieline2Reservation_Tieline2, Tieline2Reservation_ControlSystem_Name, Tieline2Reservation_User, Tieline2Reservation_IPAddress,  Tieline2Reservation_LTC, Tieline2Reservation_Timestamp,
				Tieline2Reservation_SourcePhysLevel, Tieline2Reservation_SourceSignalType, Tieline2Reservation_SourcePort,  
				Tieline2Reservation_DestinationPhysLevel, Tieline2Reservation_DestinationSignalType, Tieline2Reservation_DestinationPort, 
				Tieline2Reservation_UpstreamPort, Tieline2Reservation_DownstreamPort ) 
		VALUES ( @intTieline2In, @vchSrcCtrlSyNameIn, @intNvUserIn, @vchIPAddressIn, @vchLTCIn, @dtmCurrentTimestampIn,
			 @intSrcPhysLevelIn,  @intSrcSignalTypeIn, @intSrcPortIn, 
			 @intDestPhysLevelIn, @intDestSignalTypeIn, @intDestPortIn, 
			 @intUpPortIn, @intDownPortIn)  
	END
	IF ( @@error = 0 )
		SET @intSuccess = 1

	IF ( @vchShareType = 'S' )
		SET @intStatus = 66				-- Destination is different
	
END
ELSE IF ( @intDropAndReserve = 1 )
BEGIN
	--PRINT 'intDropAndReserve = 1 ... do delete'

	--We need to commit this delete, do a rollback incase any other segments have been reserved. We can't
	-- use this path.

	SET @intStatus = 99  	--Flag caller that this segment needs to be deleted. When I tried
							-- to do a rollback and then a delete here after returning to the called
							-- I got a "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION 
							-- statement is missing. Previous count = 1, current count = 0" error. So the procedure
							-- that started the transaction need to do the rollback/commit
	SET @intSuccess = 0
		
	-- We need to do another new reservation, but we're done here. Don't return 0 (okay) return 1 (failure).
END
ELSE IF ( @intUpdateSource = 1 ) 
BEGIN	
	-- We can only change the source port number if this is the 1st hop
	IF ( @intHopCountIn = 1 )
	BEGIN
		--PRINT 'intUpdateSource = 1 ... calling NV_ReleaseExistingTieline2Path'
		-- Same user, same destination, but new source. Update the source port number and release any upstream segments
		SET @intStatus = 0
		EXEC @intStatus = NV_ReleaseUpstreamTieline2Path
							@intTieline2In,
							@intNvUserIn,
							@intSrcPortIn,
							@intDestPhysLevelIn,
							@intUpPortIn,
							@intDestPortIn
							
		IF ( @intStatus = 0 )
		BEGIN
			SET @intSuccess = 1
			SET @intStatus = 88		-- the source port has changed
		END
	END
	
END

IF ( @intStatus = -1 )
BEGIN
	--PRINT '@intStatus is undefined in NV_ReserveTieline2Segment'
	IF ( @intSuccess = 0 )
		SET @intStatus =  1
	ELSE
		SET @intStatus = 0
END

/*
IF ( @intStatus = 0 )
	--EXEC master..xp_cmdshell 'echo exiting, status is 0 >> c:\SQLDebugFile'
else
	--EXEC master..xp_cmdshell 'echo exiting, status is not 0 >> c:\SQLDebugFile'
*/

RETURN @intStatus
									

--PRINT ' Exiting  NV_ReserveTieline2Segment, returning: '  + CONVERT(VARCHAR, @intStatus)

GO

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

CREATE PROCEDURE NV_ReserveIndividualTieline2
				@intNvUserIn		integer,
				@vchIPAddressIn 	varchar(20),
				@vchLTCIn 			varchar(20),
				@intTieline2In		integer,
				@intSrcPhysLevelIn 	 integer,
				@intSrcSignalTypeIn integer,
				@intSrcPortIn		integer,
				@intDestPhysLevelIn integer,
				@intDestSignalTypeIn integer,
				@intDestPortIn		integer,
				@intDownPortIn		integer,
				@intUpPortIn		integer
AS

--PRINT 'Entering NV_ReserveIndividualTieline2'

declare @vchSrcCtrlSysName 		varchar(24)
declare @dtmCurrentTimestamp 	datetime

SET @vchSrcCtrlSysName = ( SELECT ControlSystem_Name FROM ControlSystem WHERE ControlSystem_ID = 1 )

SET @dtmCurrentTimestamp = GETDATE()

INSERT into Tieline2Reservation ( 
		Tieline2Reservation_Tieline2, Tieline2Reservation_ControlSystem_Name, Tieline2Reservation_User, 
		Tieline2Reservation_IPAddress,  Tieline2Reservation_LTC, Tieline2Reservation_Timestamp,
		Tieline2Reservation_SourcePhysLevel, Tieline2Reservation_SourceSignalType, Tieline2Reservation_SourcePort,  
		Tieline2Reservation_DestinationPhysLevel, Tieline2Reservation_DestinationSignalType, Tieline2Reservation_DestinationPort, 
		Tieline2Reservation_UpstreamPort, Tieline2Reservation_DownstreamPort ) 
VALUES ( @intTieline2In, @vchSrcCtrlSysName, @intNvUserIn, @vchIPAddressIn, @vchLTCIn, @dtmCurrentTimestamp,
	 @intSrcPhysLevelIn,  @intSrcSignalTypeIn, @intSrcPortIn, 
	 @intDestPhysLevelIn, @intDestSignalTypeIn, @intDestPortIn, 
	 @intUpPortIn, @intDownPortIn)  

GO
