SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_sequence_next_number]
	@sequence_code ais_code_short,				-- code séquence
	@actor_id int,								-- id de l'entité juridique, utilisé si le prefix contient &b
	@department_code ais_code_medium,			-- code du département, utilisé si le prefix contient &d
	@max_len_allowed TINYINT,
	@next_number ais_code_long OUTPUT
AS 

	SET NOCOUNT ON;


	/*
	DGE 23/02/2014
	Ajouter des compteurs par type de préfix.
	Dans les numérotations, on peut utiliser des "magic strings":
		&b - branch code (code abbregé de l'entité juridique);
		&d - department code (code abbregé département);
		&s - siècle & année;
		&a - année;
		&m - mois.


	Exemple: #b-#d#s#m:   PYF_1-D1201402


	Attention!
	La longueur totale du préfix est ais_code_medium. En plus, après avoir remplacé les "magic strings", la taille totale doit être cohérente 
	par rapport à la valeur @max_len_allowed.

	*/

	DECLARE @intErrorCode INT
	DECLARE @seqc_counter_value INT
	DECLARE @seq_counter_size TINYINT 
	DECLARE @seq_pad_zero bit 
	DECLARE @seq_prefix ais_code_medium
	DECLARE @suffix ais_code_medium
	DECLARE @len_counter_value TINYINT
	DECLARE @error_no_more_numbers bit
	DECLARE @error_sequence_not_found bit
	
	SET @error_no_more_numbers = 0
	SET @error_sequence_not_found = 0
	
	-- obtenir les informations concernant la séquence
	select	@seq_prefix = s.seq_prefix,
			@seq_counter_size = s.seq_counter_size,
			@seq_pad_zero = s.seq_pad_zero
	from	sq_sequences s
	where	seq_sequence_code = @sequence_code


	-- générer le nouveau préfix
	declare		@s ais_code_medium		-- chaîne qui remplace la balise

	-- branche
	if charindex('&b', @seq_prefix) > 0
	begin
		select	@s = coalesce(b.br_short_code, '')
				--@s = coalesce(b.br_short_code, a.act_internal_reference)
		from	act_branches b
				inner join act_actors a on a.act_actor_id = b.br_actor_id
		where	b.br_actor_id = @actor_id

		select @seq_prefix = REPLACE(@seq_prefix, '&b', @s)
	end


	-- département
	if charindex('&d', @seq_prefix) > 0
	begin
		
		select	@s =  coalesce(d.brdep_short_code, '') 
				--@s =  coalesce(d.brdep_short_code, d.brdep_department_code) 
		from	act_branches_departments d
		where	d.brdep_branch_id = @actor_id
		and		d.brdep_department_code = @department_code

		select @seq_prefix = REPLACE(@seq_prefix, '&d', @s)
	end

	-- siècle
	if charindex('&s', @seq_prefix) > 0
	begin
		select @s = convert(varchar(4), year(getdate()))
		select @seq_prefix = REPLACE(@seq_prefix, '&s', @s)
	end

	-- année
	if CHARINDEX('&a', @seq_prefix) > 0
	begin
		select	@s = right(convert(varchar(4), year(getdate())), 2)

		select @seq_prefix = REPLACE(@seq_prefix, '&a', @s)
	end
	
	-- mois
	if CHARINDEX('&m', @seq_prefix) > 0
	begin
		select	@s = right('00' + convert(varchar(2), month(getdate())), 2)

		select @seq_prefix = REPLACE(@seq_prefix, '&m', @s)
	end



	-- recherche du compteur et bloquer la ligne
	SELECT	@seqc_counter_value = seqc_counter_value + 1
	FROM	sq_sequences_counters c WITH (HOLDLOCK, ROWLOCK) 
	WHERE	c.seqc_sequence_code = @sequence_code
	and		c.seqc_prefix = @seq_prefix

	
	if @seqc_counter_value is null
	begin
		select @seqc_counter_value = 1
	end

	IF @seq_prefix IS NULL and isnull(@seq_counter_size,0) = 0
	BEGIN
		-- sequence not found
		SET @error_sequence_not_found = 1
		GOTO ERROR_OCCURED
	END
	
	-- construct the number 
	IF @seq_pad_zero = 0
		SET @suffix = CONVERT(VARCHAR, @seqc_counter_value)
	ELSE
	BEGIN
		SET @len_counter_value = LEN(CONVERT(VARCHAR, @seqc_counter_value))
		
		IF @len_counter_value <= @seq_counter_size
			SET @suffix = REPLICATE('0', @seq_counter_size - @len_counter_value) + CONVERT(VARCHAR, @seqc_counter_value)
		ELSE
		BEGIN
			-- no more numbers available
			SET @error_no_more_numbers = 1
			GOTO ERROR_OCCURED
		END
	END 
	
	SET @next_number = convert(varchar,isnull(@seq_prefix,'')) + convert(varchar,@suffix)
	
	-- test if result greater than max number of characters allowed
	IF @max_len_allowed < LEN(@next_number)
	BEGIN
		-- no more numbers available
		SET @error_no_more_numbers = 1
		GOTO ERROR_OCCURED
	END
	
    
	SELECT @intErrorCode = @@ERROR
    IF @intErrorCode <> 0 GOTO ERROR_OCCURED

    -- if all ok, insert the the current counter
	insert into sq_sequences_counters(seqc_sequence_code, seqc_prefix, seqc_counter_value, seqc_number)
	values (@sequence_code, @seq_prefix, @seqc_counter_value, @next_number)
	
	GOTO QUIT_PROCEDURE
	
ERROR_OCCURED:

	
	SET @next_number = NULL
	
	IF @error_sequence_not_found = 1
		RAISERROR('Sequence not found: %d',10,1,-100)
	ELSE
		IF @error_no_more_numbers = 1
			RAISERROR('No more numbers available: %d',10,1,-200)
		ELSE 
			RAISERROR('Error: %d',10,1,@intErrorCode) 
		
QUIT_PROCEDURE:







GO
