
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[f_format_variable]
(
	@value			ais_decimal2,
	@fmt_code		ais_code_short,
	@culture_code	ais_code_short,
	@currency_code	ais_code_short
)
RETURNS NVARCHAR(64)
AS
BEGIN

	DECLARE	
		@result			NVARCHAR(64),
		@format			NVARCHAR(64),
		@precision		SMALLINT,
		@value_str		NVARCHAR(64),
		@fmt_monetary	bit,
		@tsep			nvarchar(2) = ' ',
		@dsep			nvarchar(2) = ',',
		@language_code	ais_code_short,
		@cur_symbol_position_left bit,
		@cur_symbol		nvarchar(10),
		@fmt_transformation_type_code ais_code_short 

	
	
	select	@language_code = clt_language_code 
	from	md_cultures (nolock)
	where	clt_culture_code = @culture_code
		
	SELECT	@format =	case	
						when	@language_code = 'l3' then coalesce(fmt_format_l3, fmt_format_l1)
						when	@language_code = 'l2' then coalesce(fmt_format_l2, fmt_format_l1)
						else	fmt_format_l1
						end,
			@precision = case when fmt_do_not_show_decimals = 0 then fmt_precision else 0 end,
			@fmt_transformation_type_code = nf.fmt_transformation_type_code
	FROM	nm_formats (nolock) nf
		--left join nm_formats_transformation_types nftt on nftt.nftt_transformation_code = nf.fmt_transformation_type_code
	where	fmt_fmt_code = @fmt_code
	
	if len(isnull(@format, '')) = 0 
	begin
		-- si pas de format défini, on considère qu'il s'agit d'un format monétaire
		select @format = '*', @fmt_transformation_type_code = 'CUR'
			
		--return convert(nvarchar(64), @value)
	end
		
		
	if charindex('*',@format) > 0
	begin

		-- nm_formats_transformation_types
		-- CUR = currency
		-- NUMWTS - Number with thousands separator
		-- NUMOTS - Number without thousands separator

		if isnull(@fmt_transformation_type_code,'') = 'NUMOTS' set @tsep = '' -- pas de séparateur de miliers

		if isnull(@fmt_transformation_type_code,'') = 'CUR'
		begin

			select	@cur_symbol = ' ' + ltrim(rtrim(cur_symbol)),
					@cur_symbol_position_left = cur_symbol_position_left,
					@precision = case when cur_use_decimals = 1 then 2 else 0 end
			from acc_currencies
			where cur_currency_code = @currency_code

		end

		SET @precision = ISNULL(@precision,0)

		set @result = dbo.f_format_number(@value, @precision, @tsep, @dsep)

		if isnull(@fmt_transformation_type_code,'') = 'CUR'
		begin
			if len(@result) > 0 
				set @result =		case when @cur_symbol_position_left = 1 then isnull(@cur_symbol,'') else '' end
								+	@result
								+	case when @cur_symbol_position_left = 0 then isnull(@cur_symbol,'') else '' end	
		end
				
		set @result = replace(@format, '*', @result)
	end
	else
	begin
		set @result = @format
	end
	
	/*
	SET @result =	CASE
							WHEN charindex('*',@format) > 0 and (@fmt_monetary = 0 or @precision = 0) THEN 
									substring(stuff(@format, charindex('*',@format), 1,substring(@value_str,1 , charindex('.', @value_str) -1)),1,128)

							WHEN @fmt_monetary=1 and charindex('*',@format) > 0 and @precision > 0 THEN 
									substring(
										stuff(
											@format, charindex('*',@format), 1, 
												substring(@value_str,1 , charindex('.',  @value_str) -1 ) + @dsep +
												substring(@value_str,charindex('.',  @value_str)+ 1, @precision)  +
												replicate('0', @precision - len(substring(@value_str,charindex('.', @value_str)+ 1, @precision)))
											  )       
										,1,128
									)
				
							ELSE @format
					END
	*/
							
	return @result
	
	
END
-----------------------------------------------------------------------------------------------------------------------
	




GO
