USE [VIGIL-POS]
GO

-- DROP

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[Split]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_nosale_after_void]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_nosale_after_void]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_previous_receipt_no]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_previous_receipt_no]
GO


-- CREATE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_nosale_after_void]
(@start_time as DATETIME,
@end_time as DATETIME,
@register_num as INT = 0,
@min_idx as BIGINT = 0)
RETURNS @void_nosale TABLE (receipt_no nvarchar(20))
AS
BEGIN
	DECLARE @terminal_cur AS INT 
	DECLARE @register_cur AS INT 
	DECLARE @receipt_cur AS NVARCHAR(20)
	DECLARE @idx_cur AS BIGINT
	DECLARE @void_cur AS BIT 
	DECLARE @nosale_cur AS BIT 
	
	DECLARE @terminal_prev AS INT 
	DECLARE @register_prev AS INT 
	DECLARE @receipt_prev AS NVARCHAR(20)
	DECLARE @idx_prev AS BIGINT
	DECLARE @void_prev AS BIT 
	DECLARE @nosale_prev AS BIT 


	DECLARE curReceipt CURSOR FOR 
		SELECT terminal_num, register_num, receipt_no, idx, void, nosale 
		  FROM posdata 
		 WHERE idx >= @min_idx 
		   AND server_time BETWEEN @start_time AND @end_time
		   AND (@register_num <= 0 OR register_num = @register_num)
		   AND receipt_no IS NOT NULL 
		   AND len(receipt_no) > 0 
		   AND (void = 1 OR nosale = 1)
	  GROUP BY terminal_num, register_num, receipt_no, idx, void, nosale 
	  ORDER BY receipt_no, idx

	OPEN curReceipt
	FETCH curReceipt INTO @terminal_cur, @register_cur, @receipt_cur, @idx_cur, @void_cur, @nosale_cur
	SET @terminal_prev  = @terminal_cur
	SET @register_prev = @register_cur 
	SET @receipt_prev = @receipt_cur
	SET @idx_prev = @idx_cur
	SET @void_prev = @void_cur 
	SET @nosale_prev = @nosale_cur 


	WHILE @@FETCH_STATUS = 0
	BEGIN 
		IF @terminal_prev = @terminal_cur AND @register_prev = @register_cur AND (@receipt_prev = @receipt_cur OR ISNUMERIC(@receipt_prev) = 1 AND ISNUMERIC(@receipt_cur) = 1 AND @receipt_prev + 1 = @receipt_cur) AND @void_prev = 1 AND @nosale_cur = 1
		BEGIN 
			INSERT INTO @void_nosale(receipt_no) VALUES (@receipt_prev)
			INSERT INTO @void_nosale(receipt_no) VALUES (@receipt_cur)
		END
		
		SET @terminal_prev  = @terminal_cur
		SET @register_prev = @register_cur 
		SET @receipt_prev = @receipt_cur
		SET @idx_prev = @idx_cur
		SET @void_prev = @void_cur 
		SET @nosale_prev = @nosale_cur 
	
		FETCH curReceipt INTO @terminal_cur, @register_cur, @receipt_cur, @idx_cur, @void_cur, @nosale_cur
	END

	RETURN
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split](@String nvarchar (4000), @Delimiter nvarchar (10))
RETURNS @ValueTable TABLE ([Value] nvarchar(4000))
BEGIN
	declare @NextString nvarchar(4000)
	declare @Pos int
	declare @NextPos int
	declare @CommaCheck nvarchar(1)

	--Initialize
	set @NextString = ''
	set @CommaCheck = right(@String,1) 

	--Check for trailing Comma, if not exists, INSERT
	--if (@CommaCheck <> @Delimiter )
	set @String = @String + @Delimiter

	--Get position of first Comma
	set @Pos = charindex(@Delimiter,@String)
	set @NextPos = 1
 
	--Loop while there is still a comma in the String of levels
	while (@pos <>  0)  
	BEGIN
		set @NextString = ltrim (substring(@String,1,@Pos - 1))

		insert into @ValueTable ( [Value]) Values (@NextString)

		set @String = substring(@String,@pos +1,len(@String))

		set @NextPos = @Pos
		set @pos  = charindex(@Delimiter,@String)
	END
 RETURN
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_get_previous_receipt_no]
(
@receipt_no nvarchar(20),
@terminal int,
@register int,
@min_server_time datetime
)
RETURNS nvarchar(20)
AS
BEGIN
	DECLARE @min_idx bigint
	DECLARE @previous_receipt_no nvarchar(20)

	SELECT @min_idx = min(idx) FROM POSData WHERE receipt_no = @receipt_no AND terminal_num = @terminal AND register_num = @register AND server_time >= @min_server_time

	SELECT TOP 1 @previous_receipt_no = receipt_no FROM POSData
	WHERE terminal_num = @terminal AND register_num = @register AND idx < @min_idx AND idx >= @min_idx - 5000 AND receipt_no != @receipt_no AND receipt_no IS NOT NULL AND receipt_no != ''
	ORDER BY idx DESC

	RETURN @previous_receipt_no
END
GO
