DELIMITER $$

USE `test_vitalis`$$


DROP FUNCTION IF EXISTS `fn_nombreEnLettres_rp`$$ 
 
CREATE DEFINER=`vitalis`@`%` FUNCTION `fn_nombreEnLettres_rp`(
	p_nombre BIGINT 
)
RETURNS VARCHAR(255) CHARSET utf8
BEGIN
	DECLARE p_lettres VARCHAR(200) DEFAULT '' ;
	DECLARE p_diviseur BIGINT;
	DECLARE p_millier BIGINT;
	DECLARE p_centaine BIGINT;
	DECLARE p_dizaine BIGINT;
	DECLARE p_unite BIGINT;
	DECLARE p_reste BIGINT;
	DECLARE p_courant BIGINT;
	DECLARE p_dix BIT DEFAULT 0;
	DECLARE p_et BIT;
	DECLARE p_ajout VARCHAR(200);

	SET p_diviseur = 1000000000 ;
	SET p_reste = p_nombre ;

	WHILE p_diviseur >= 1
	BEGIN
		SELECT p_courant = p_reste / p_diviseur;
		IF p_courant <> 0
		BEGIN
			SELECT p_millier = p_courant / 1000 ;
			SELECT p_centaine = p_courant / 100 ;
			SELECT p_dizaine = (p_courant - p_centaine * 100) / 10 ;
			SELECT p_unite = p_courant - (p_centaine * 100) - (p_dizaine * 10);
			SELECT p_ajout = '' ;
			
			SELECT p_ajout = p_ajout +
				CASE p_millier
				WHEN 1 THEN 'mille '
				WHEN 2 THEN 
					CASE WHEN p_centaine = 0 AND p_dizaine = 0 AND p_unite = 0 THEN 'deux milles ' ELSE 'deux mille ' END
				WHEN 3 THEN 
					CASE WHEN p_centaine = 0 AND p_dizaine = 0 AND p_unite = 0 THEN 'trois milles ' ELSE 'trois mille ' END
				WHEN 4 THEN 
					CASE WHEN p_centaine = 0 AND p_dizaine = 0 AND p_unite = 0 THEN 'quatre milles ' ELSE 'quatre mille ' END
				WHEN 5 THEN 
					CASE WHEN p_centaine = 0 AND p_dizaine = 0 AND p_unite = 0 THEN 'cinq milles ' ELSE 'cinq mille ' END
				WHEN 6 THEN 
					CASE WHEN p_centaine = 0 AND p_dizaine = 0 AND p_unite = 0 THEN 'six milles ' ELSE 'six mille ' END
				WHEN 7 THEN 
					CASE WHEN p_centaine = 0 AND p_dizaine = 0 AND p_unite = 0 THEN 'sept milles ' ELSE 'sept mille ' END
				WHEN 8 THEN 
					CASE WHEN p_centaine = 0 AND p_dizaine = 0 AND p_unite = 0 THEN 'huit milles ' ELSE 'huit mille ' END
				WHEN 9 THEN 
					CASE WHEN p_dizaine = 0 AND p_unite = 0 THEN 'neuf milles ' ELSE 'neuf mille ' END
				ELSE ''
				END ;
			
			SELECT p_ajout = p_ajout +
				CASE p_centaine
				WHEN 1 THEN 'cent '
				WHEN 2 THEN 
					CASE WHEN p_dizaine = 0 AND p_unite = 0 THEN 'deux cents ' ELSE 'deux cent ' END
				WHEN 3 THEN 
					CASE WHEN p_dizaine = 0 AND p_unite = 0 THEN 'trois cents ' ELSE 'trois cent ' END
				WHEN 4 THEN 
					CASE WHEN p_dizaine = 0 AND p_unite = 0 THEN 'quatre cents ' ELSE 'quatre cent ' END
				WHEN 5 THEN 
					CASE WHEN p_dizaine = 0 AND p_unite = 0 THEN 'cinq cents ' ELSE 'cinq cent ' END
				WHEN 6 THEN 
					CASE WHEN p_dizaine = 0 AND p_unite = 0 THEN 'six cents ' ELSE 'six cent ' END
				WHEN 7 THEN 
					CASE WHEN p_dizaine = 0 AND p_unite = 0 THEN 'sept cents ' ELSE 'sept cent ' END
				WHEN 8 THEN 
					CASE WHEN p_dizaine = 0 AND p_unite = 0 THEN 'huit cents ' ELSE 'huit cent ' END
				WHEN 9 THEN 
					CASE WHEN p_dizaine = 0 AND p_unite = 0 THEN 'neuf cents ' ELSE 'neuf cent ' END
				ELSE ''
				END ;

			SELECT p_ajout = p_ajout +
				CASE p_dizaine
				WHEN 2 THEN 'vingt '
				WHEN 3 THEN 'trente '
				WHEN 4 THEN 'quarante '
				WHEN 5 THEN 'cinquante '
				WHEN 6 THEN 'soixante '
				WHEN 7 THEN 'soixante '
				WHEN 8 THEN 'quatre-vingt '
				WHEN 9 THEN 'quatre-vingt '
				ELSE ''
				END ;
			
			SELECT p_dix = CASE p_dizaine
				WHEN 1 THEN 1
				WHEN 7 THEN 1
				WHEN 9 THEN 1
				ELSE 0
				END ;

			SELECT p_et = CASE p_dizaine
				WHEN 0 THEN 0
				WHEN 1 THEN 0
				WHEN 2 THEN 1
				WHEN 3 THEN 1
				WHEN 4 THEN 1
				WHEN 5 THEN 1
				WHEN 6 THEN 1
				WHEN 7 THEN 1
				WHEN 8 THEN 0
				WHEN 9 THEN 0
				ELSE 0
				END ;

			SELECT p_ajout = p_ajout +
				CASE p_unite
				WHEN 0 THEN
					CASE p_dix WHEN 1 THEN 'dix ' ELSE '' END
				WHEN 1 THEN
					CASE p_et WHEN 1 THEN 'et ' ELSE '' END +
					CASE p_dix WHEN 1 THEN 'onze ' ELSE 'un ' END
				WHEN 2 THEN
					CASE p_dix WHEN 1 THEN 'douze ' ELSE 'deux ' END
				WHEN 3 THEN
					CASE p_dix WHEN 1 THEN 'treize ' ELSE 'trois ' END
				WHEN 4 THEN
					CASE p_dix WHEN 1 THEN 'quatorze ' ELSE 'quatre ' END
				WHEN 5 THEN
					CASE p_dix WHEN 1 THEN 'quinze ' ELSE 'cinq ' END
				WHEN 6 THEN
					CASE p_dix WHEN 1 THEN 'seize ' ELSE 'six ' END
				WHEN 7 THEN
					CASE p_dix WHEN 1 THEN 'dix-sept ' ELSE 'sept ' END
				WHEN 8 THEN
					CASE p_dix WHEN 1 THEN 'dix-huit ' ELSE 'huit ' END
				WHEN 9 THEN
					CASE p_dix WHEN 1 THEN 'dix-neuf ' ELSE 'neuf ' END
				ELSE ''
				END  ;

			SELECT p_lettres = p_lettres +	
				CASE p_diviseur
				WHEN 1000000000 THEN
					CASE
					WHEN p_courant = 1 THEN 'un milliard ' 
					WHEN p_courant > 1 THEN p_ajout + 'milliards '
					END 
				WHEN 1000000 THEN
					CASE
					WHEN p_courant = 1 THEN 'un million ' 
					WHEN p_courant > 1 THEN p_ajout + 'millions '
					END 
				WHEN 1000 THEN
					CASE WHEN p_courant > 1 THEN p_ajout ELSE '' END + 'mille ' 
				ELSE p_ajout
				END  ;

			SELECT p_reste = p_reste - p_courant * p_diviseur ;
		END
		SELECT p_diviseur = p_diviseur / 1000 ;
	END

	IF p_lettres = '' SELECT p_lettres = 'zéro' ;
	
	RETURN IFNULL(UPPER(LTRIM(RTRIM(p_lettres))), '');
END$$

DELIMITER ;


