USE [ShipLinkCentralDB]
GO
/****** Object:  StoredProcedure [dbo].[GetNextCaseNumber]    Script Date: 03/14/2023 6:22:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[GetNextNumber]
  @fieldName varchar(50),
  @incrementBy int,
  @digitLimit int,
  @newValue varchar(500) OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @currentValue varchar(500);

	BEGIN TRANSACTION
  
		-- Handle null values and initialize to 0 if no record found
		SELECT @currentValue = FieldValue FROM SLCSetting With (TABLOCKX) WHERE FieldName = @fieldName

		IF @currentValue IS NULL
		BEGIN
		SET @currentValue = '0'
		INSERT INTO SLCSetting (FieldName, FieldValue) VALUES (@fieldName, @currentValue)
		END

		DECLARE @currentNumber int = CAST(@currentValue AS int);

		-- Increment the value and update the table
		set @currentNumber = @currentNumber + @incrementBy

		-- Check if current value is at the maximum
		if @currentNumber > CAST(REPLICATE('9', @digitLimit) AS int)
		begin
		set @currentNumber = @incrementBy
		end

		SET @currentValue = CAST(@currentNumber AS varchar(9))
	  
		UPDATE SLCSetting SET FieldValue = @currentValue WHERE FieldName = @fieldName

	COMMIT TRANSACTION
	-- Return the new value through the OUTPUT parameter
	SET @newValue = @currentValue;
END
create PROCEDURE [dbo].[GetNextCaseNumber]
  @fieldName varchar(50),
  @incrementBy int,
  @newValue varchar(500) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @currentValue varchar(500);
  DECLARE @year int = Right(YEAR(GETDATE()), 2);

  BEGIN TRANSACTION
  
	  -- Handle null values and initialize to 1 if no record found
	  SELECT @currentValue = FieldValue FROM SLCSetting With (TABLOCKX) WHERE FieldName = @fieldName

	  IF @currentValue IS NULL
	  BEGIN
		SET @currentValue = CAST(@year as varchar(2)) + '000000'
		INSERT INTO SLCSetting (FieldName, FieldValue) VALUES (@fieldName, @currentValue)
	  END

	  DECLARE @currentYear int = CAST(LEFT(@currentValue, 2) as int);
	  DECLARE @currentNumber int = CAST(RIGHT(@currentValue, 6) AS int);

	  if @year > @currentYear
	  BEGIN
		set @currentYear = @year
		set @currentNumber = 0
	  END

	  -- Increment the value and update the table
	  set @currentNumber = @currentNumber + @incrementBy

	  -- Check if current value is at the maximum for the year
	  if @currentNumber > 999999
	  begin
		set @currentNumber = @incrementBy
		set @currentYear = @currentYear + 10
	  end

	  SET @currentValue = CAST(@currentYear as varchar(2)) + RIGHT('000000' + CAST(@currentNumber AS varchar(6)), 6)
	  
	  UPDATE SLCSetting SET FieldValue = @currentValue WHERE FieldName = @fieldName

  COMMIT TRANSACTION
  -- Return the new value through the OUTPUT parameter
  SET @newValue = @currentValue;
END