/****** Object:  StoredProcedure [dbo].[IPRangeBatchEx_se]    Script Date: 16/01/2017 11:56:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[IPRangeBatchEx_se]
(
	@ServerID		SMALLINT,
	@ServiceType	NVARCHAR(50)
)
AS
BEGIN TRY
	BEGIN TRANSACTION

	DECLARE @NewTargets TABLE
	(
		[Sequence]		INT IDENTITY(1,1),
		[IPRangeID]		INT,
		[IPRangeType]	VARCHAR(50),
		[StartIP]		VARCHAR(15),
		[EndIP]			VARCHAR(15)
	);


	DECLARE @Error	BIT;
	
	--DELETE FROM [IPRangeBatch] WHERE [IPRangeID] IN ( SELECT [IPRangeID] FROM [IPRange] WHERE [StartIPBinary] IS NULL OR [EndIPBinary] IS NULL );
	

    UPDATE IPRange Set Completed=0 WHERE  ISNULL(DATEDIFF(minute , DATEADD(minute,[RescanInterval], [CompletedDate]), getdate() ),0) > 0  AND Completed =1
  
	INSERT INTO @NewTargets(IPRangeID,IPRangeType,StartIP,EndIP)
	SELECT t.[IPRangeID], ty.[IPRangeTypeEnum], substring(LTRIM(RTRIM(t.[StartIP])),1,15), substring(LTRIM(RTRIM(t.[EndIP])),1,15)
	FROM IPRange t
	JOIN IPRangeTypeEnum ty on t.IPRangeTypeEnumID=ty.IPRangeTypeEnumID
	INNER JOIN [MapIPRange_Server] AS mis ON mis.[IPRangeID] = t.[IPRangeID]
	INNER JOIN [MapLocation_Server] AS mls ON mls.[LocationID] = t.[LocationID]
	WHERE (t.[Completed] IS NULL OR t.[Completed] = 0  ) AND mis.[_ServerID] = @ServerID 
	AND mls.[_ServerID] = @ServerID
	AND NOT EXISTS(SELECT * FROM [IPRangeBatch] WHERE [IPRangeID]=t.[IPRangeID])

	INSERT INTO @NewTargets(IPRangeID,IPRangeType,StartIP,EndIP)
	SELECT t.[IPRangeID], ty.[IPRangeTypeEnum], substring(LTRIM(RTRIM(t.[StartIP])),1,15), substring(LTRIM(RTRIM(t.[EndIP])),1,15)
	FROM IPRange t
	JOIN IPRangeTypeEnum ty on t.IPRangeTypeEnumID=ty.IPRangeTypeEnumID
	INNER JOIN [MapIPRange_Server] AS mis ON mis.[IPRangeID] = t.[IPRangeID]
	WHERE (t.[Completed] IS NULL OR t.[Completed] = 0  ) AND mis.[_ServerID] = @ServerID 
	AND NOT EXISTS(SELECT * FROM [IPRangeBatch] WHERE [IPRangeID]=t.[IPRangeID])
	AND t.[LocationID] IS NULL AND NOT EXISTS (SELECT 1 FROM dbo.[MapLocation_Server] WHERE [_ServerID] = @ServerID)


	IF EXISTS(SELECT TOP 1 * FROM @NewTargets)
	BEGIN
		DECLARE	@Sequence		INT,
				@MaxSequence	INT,
				@IPRangeID	INT,
				@IPRangeType		VARCHAR(50),
				@StartIP	VARCHAR(15),
				@EndIP		VARCHAR(15);

		SELECT @Sequence = MIN(Sequence), @MaxSequence = MAX(Sequence) FROM @NewTargets;

		WHILE(@Sequence <= @MaxSequence)
		BEGIN
			SET @Error = 0;
			SET @IPRangeID = NULL;
			SET @IPRangeType = NULL;
			SET @StartIP = NULL;
			SET @EndIP = NULL;
			
			SELECT
				@IPRangeID = IPRangeID,
				@IPRangeType = IPRangeType,
				@StartIP = StartIP,
				@EndIP = EndIP
			FROM @NewTargets WHERE Sequence = @Sequence;
			
			
			DECLARE @IPPattern		VARCHAR(48);
			SET @IPPattern = '\b(?:\d{1,3}\.){3}\d{1,3}\b';
			
			IF dbo.RegExMatch(@EndIP, @IPPattern, 0) = 0
			BEGIN
				UPDATE [IPRange] SET [LastAttemptError] = 'Invalid end IP address (should be 4 numbers separated by dots)' WHERE [IPRangeID] = @IPRangeID;
				SET @Error = 1				
			END;
			
			IF dbo.RegExMatch(@StartIP, @IPPattern , 0) = 0
			BEGIN
				UPDATE [IPRange] SET [LastAttemptError] = 'Invalid start IP address (should be 4 numbers separated by dots)' WHERE [IPRangeID] = @IPRangeID;
				SET @Error = 1				
			END;

			IF @Error = 1
			BEGIN
				SET @Sequence = @Sequence + 1;
				CONTINUE;
			END;

			DECLARE @StartIPByte1	TINYINT,
					@StartIPByte2	TINYINT,
					@StartIPByte3	TINYINT,
					@StartIPByte4	TINYINT,
					@EndIPByte1		TINYINT,
					@EndIPByte2		TINYINT,
					@EndIPByte3		TINYINT,
					@EndIPByte4		TINYINT,
					@lastPos		INT,
					@pos			INT;
			
			SET @pos = CHARINDEX('.',@StartIP);
			IF (@pos = 0) RAISERROR('Invalid StartIP on IPRangeID %d',15,1,@IPRangeID)
			SET @StartIPByte1 = CAST(SUBSTRING(@StartIP, 1, @pos - 1) AS TINYINT)
			SET @lastPos = @pos;
			SET @pos = CHARINDEX('.',@StartIP,@lastPos + 1);
			IF (@pos = 0) RAISERROR('Invalid StartIP on IPRangeID %d',15,1,@IPRangeID)
			SET @StartIPByte2 = CAST(SUBSTRING(@StartIP, @lastPos + 1, @pos - (@lastPos + 1)) AS TINYINT)
			SET @lastPos = @pos;
			SET @pos = CHARINDEX('.',@StartIP,@lastPos + 1);
			IF (@pos = 0) RAISERROR('Invalid StartIP on IPRangeID %d',15,1,@IPRangeID)
			SET @StartIPByte3 = CAST(SUBSTRING(@StartIP, @lastPos + 1, @pos - (@lastPos + 1)) AS TINYINT)
			SET @StartIPByte4 = CAST(SUBSTRING(@StartIP, @pos + 1, LEN(@StartIP) - @pos + 1) AS TINYINT)
			
			SET @pos = CHARINDEX('.',@EndIP);
			IF (@pos = 0) RAISERROR('Invalid EndIP on IPRangeID %d',15,1,@IPRangeID)
			SET @EndIPByte1 = CAST(SUBSTRING(@EndIP, 1, @pos - 1) AS TINYINT)
			SET @lastPos = @pos;
			SET @pos = CHARINDEX('.',@EndIP,@lastPos + 1);
			IF (@pos = 0) RAISERROR('Invalid EndIP on IPRangeID %d',15,1,@IPRangeID)
			SET @EndIPByte2 = CAST(SUBSTRING(@EndIP, @lastPos + 1, @pos - (@lastPos + 1)) AS TINYINT)
			SET @lastPos = @pos;
			SET @pos = CHARINDEX('.',@EndIP,@lastPos + 1);
			IF (@pos = 0) RAISERROR('Invalid EndIP on IPRangeID %d',15,1,@IPRangeID)
			SET @EndIPByte3 = CAST(SUBSTRING(@EndIP, @lastPos + 1, @pos - (@lastPos + 1)) AS TINYINT)
			SET @EndIPByte4 = CAST(SUBSTRING(@EndIP, @pos + 1, LEN(@EndIP) - @pos + 1) AS TINYINT)

			IF(LOWER(@IPRangeType) = 'subnet') --Convert to IP Range
			BEGIN
				--Validate the subnet mask is valid
				IF ( ( @EndIPByte1 IN ( 255, 254, 252, 248, 240, 224, 192, 128, 0 ) )
					AND ( @EndIPByte2 IN ( 255, 254, 252, 248, 240, 224, 192, 128, 0 ) )
					AND ( @EndIPByte3 IN ( 255, 254, 252, 248, 240, 224, 192, 128, 0 ) )
					AND ( @EndIPByte4 IN ( 255, 254, 252, 248, 240, 224, 192, 128, 0 ) )
				)
				BEGIN
					--Currently EndIP contains the subnet mask
					SET @StartIPByte1 = @StartIPByte1 & @EndIPByte1;
					SET @StartIPByte2 = @StartIPByte2 & @EndIPByte2;
					SET @StartIPByte3 = @StartIPByte3 & @EndIPByte3;
					SET @StartIPByte4 = @StartIPByte4 & @EndIPByte4;
					--Now StartIP contains the network ip
					SET @EndIPByte1 = @StartIPByte1 | (~@EndIPByte1);
					SET @EndIPByte2 = @StartIPByte2 | (~@EndIPByte2);
					SET @EndIPByte3 = @StartIPByte3 | (~@EndIPByte3);
					SET @EndIPByte4 = @StartIPByte4 | (~@EndIPByte4);
					--Now EndIP contains the broadcast ip

					IF (@StartIPByte1 = @EndIPByte1 AND @StartIPByte2 = @EndIPByte2 AND @StartIPByte3 = @EndIPByte3
						AND (@StartIPByte4 & 254) = (@EndIPByte4 & 254) )
					BEGIN
						--A perculiar case (this is either a /31 or /32 mask which means they don't have a network ip and broadcast ip)
						SET @pos = @pos; --This is a placeholder because SQL Server doesn't like empty BEGIN-END blocks
					END;
					ELSE
					BEGIN
						SET @StartIPByte4 = @StartIPByte4 + 1; --Ignore Network IP
						SET @EndIPByte4 = @EndIPByte4 - 1; --Ignore Broadcast IP
					END;
				END;
				ELSE
				BEGIN
					UPDATE [IPRange] SET [LastAttemptError] = 'Invalid subnet mask specified' WHERE [IPRangeID] = @IPRangeID;
					SET @Error = 1;
				END;
			END


			--Now @StartIPByteX and @EndIPByteX are an IP range, start dividing into batches
			DECLARE @CurrentIPByte1 TINYINT,
					@CurrentIPByte2 TINYINT;

			SET @CurrentIPByte1 = @StartIPByte1;
			SET @CurrentIPByte2 = @StartIPByte2;

			WHILE ( ( @Error = 0 ) AND ( @CurrentIPByte1 < @EndIPByte1 OR ( @CurrentIPByte1 = @EndIPByte1 AND @CurrentIPByte2 <= @EndIPByte2 ) ) )
			BEGIN
				DECLARE @BatchStartIP VARCHAR(15),
						@BatchEndIP VARCHAR(15);

				IF(@CurrentIPByte1 = @StartIPByte1 AND @CurrentIPByte2 = @StartIPByte2)
					SET @BatchStartIP = CAST(@CurrentIPByte1 AS VARCHAR) + '.' + CAST(@CurrentIPByte2 AS VARCHAR) + '.' + 
										CAST(@StartIPByte3 AS VARCHAR) + '.' + CAST(@StartIPByte4 AS VARCHAR);
				ELSE
					SET @BatchStartIP = CAST(@CurrentIPByte1 AS VARCHAR) + '.' + CAST(@CurrentIPByte2 AS VARCHAR) + '.0.0';

				IF(@CurrentIPByte1 = @EndIPByte1 AND @CurrentIPByte2 = @EndIPByte2)
					SET @BatchEndIP = CAST(@CurrentIPByte1 AS VARCHAR) + '.' + CAST(@CurrentIPByte2 AS VARCHAR) + '.' + 
										CAST(@EndIPByte3 AS VARCHAR) + '.' + CAST(@EndIPByte4 AS VARCHAR);
				ELSE
					SET @BatchEndIP = CAST(@CurrentIPByte1 AS VARCHAR) + '.' + CAST(@CurrentIPByte2 AS VARCHAR) + '.255.255';

				INSERT INTO IPRangeBatch(IPRangeID,StartIP,EndIP,Completed) VALUES (@IPRangeID, @BatchStartIP, @BatchEndIP, 0);

				IF(@CurrentIPByte2 < 255)
					SET @CurrentIPByte2 = @CurrentIPByte2 + 1;
				ELSE
				BEGIN
					SET @CurrentIPByte2 = 0;
					IF(@CurrentIPByte1 < 255)
					BEGIN
						SET @CurrentIPByte1 = @CurrentIPByte1 + 1;
					END
					ELSE
					BEGIN
						UPDATE [IPRange] SET [LastAttemptError] = 'Unable to find end of IP Block' WHERE [IPRangeID] = @IPRangeID;
						SET @Error = 1;
					END;
				END;
			END
			
			IF ( @Error = 0 )
				UPDATE [IPRange] SET [LastAttemptError] = NULL WHERE [IPRangeID] = @IPRangeID;

			SET @Sequence = @Sequence + 1;
		END
	END


	SELECT TOP 1 IPRangeBatchID, IPRangeID, StartIP, EndIP, LocationID FROM (
		SELECT TOP 1 b.IPRangeBatchID, b.IPRangeID, b.StartIP, b.EndIP, r.LocationID 
		FROM IPRangeBatch b
		JOIN IPRange r on b.[IPRangeID]=r.[IPRangeID]
		INNER JOIN [MapIPRange_Server] AS mis ON mis.[IPRangeID] = r.[IPRangeID]
		JOIN ServiceTypeEnum e on e.[ServiceTypeEnumID]=r.[ServiceTypeEnumID]
		WHERE mis.[_ServerID] = @ServerID AND ServiceType = @ServiceType AND b.Completed = 0
		AND (@ServiceType <> 'Discovery.Port' OR EXISTS(SELECT 1 FROM MapIPRangePortList WHERE IPRangeID=r.[IPRangeID]))
		AND r.[LocationID] IS NULL AND NOT EXISTS (SELECT 1 FROM dbo.[MapLocation_Server] WHERE [_ServerID] = @ServerID)

		UNION ALL

		SELECT TOP 1 b.IPRangeBatchID, b.IPRangeID, b.StartIP, b.EndIP, r.LocationID 
		FROM IPRangeBatch b
		JOIN IPRange r on b.[IPRangeID]=r.[IPRangeID]
		INNER JOIN [MapIPRange_Server] AS mis ON mis.[IPRangeID] = r.[IPRangeID]
		JOIN ServiceTypeEnum e on e.[ServiceTypeEnumID]=r.[ServiceTypeEnumID]
		INNER JOIN [MapLocation_Server] AS mls ON mls.[LocationID] = r.[LocationID] AND mls.IsEnabled = 1
		WHERE mis.[_ServerID] = @ServerID AND ServiceType = @ServiceType AND b.Completed = 0
		AND mls.[_ServerID] = @ServerID
		AND (@ServiceType <> 'Discovery.Port' OR EXISTS(SELECT 1 FROM MapIPRangePortList WHERE IPRangeID=r.[IPRangeID]))
	) AS resultTable
	
	COMMIT
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE	@ErrMsg NVARCHAR(4000),
			@ErrSeverity INT;

  SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();

  RAISERROR( @ErrMsg, @ErrSeverity, 1 );
END CATCH