Nouvelle catégorie "Charges cotisations" dans l'état des résultats
[auf_coda_additions.git] / db / fonctions / auf_f_montantenLettre.sql
1 USE [coda-prod]
2 GO
3
4 DROP FUNCTION [dbo].[auf_f_montantenLettre]
5 GO
6
7 SET ANSI_NULLS ON
8 GO
9 SET QUOTED_IDENTIFIER ON
10 GO
11
12
13 --SELECT dbo.auf_f_montantenLettre( 2345,' CAD')
14
15
16 CREATE FUNCTION [dbo].[auf_f_montantenLettre](@reel decimal(19,4), @devise varchar(12) ) returns varchar(150)
17
18 AS
19
20 BEGIN
21
22 DECLARE @n AS int
23 DECLARE @val AS varchar(100)
24 DECLARE @100mille AS int
25 DECLARE @10mille AS int
26 DECLARE @mille AS int
27 DECLARE @cent AS int
28 DECLARE @dix AS int
29 DECLARE @montlettre varchar(100)
30 DECLARE @vergule int
31 DECLARE @milleflag varchar(1)
32 DECLARE @m AS int
33
34 /*
35 create table auf_tt_chiffreEnLettre (
36 code int,
37 valeur varchar(30)
38 )
39
40 INSERT INTO auf_tt_chiffreEnLettre values (1,'un')
41 INSERT INTO auf_tt_chiffreEnLettre values (2,'deux')
42 INSERT INTO auf_tt_chiffreEnLettre values (3,'trois')
43 INSERT INTO auf_tt_chiffreEnLettre values (4,'quatre')
44 INSERT INTO auf_tt_chiffreEnLettre values (5,'cinq')
45 INSERT INTO auf_tt_chiffreEnLettre values (6,'six')
46 INSERT INTO auf_tt_chiffreEnLettre values (7,'sept')
47 INSERT INTO auf_tt_chiffreEnLettre values (8,'huit')
48 INSERT INTO auf_tt_chiffreEnLettre values (9,'neuf')
49 INSERT INTO auf_tt_chiffreEnLettre values (10,'dix')
50 INSERT INTO auf_tt_chiffreEnLettre values (11,'onze')
51 INSERT INTO auf_tt_chiffreEnLettre values (12,'douze')
52 INSERT INTO auf_tt_chiffreEnLettre values (13,'treize')
53 INSERT INTO auf_tt_chiffreEnLettre values (14,'quatorze')
54 INSERT INTO auf_tt_chiffreEnLettre values (15,'quinze')
55 INSERT INTO auf_tt_chiffreEnLettre values (16,'seize')
56 INSERT INTO auf_tt_chiffreEnLettre values (17,'dix-sept')
57 INSERT INTO auf_tt_chiffreEnLettre values (18,'dix-huit')
58 INSERT INTO auf_tt_chiffreEnLettre values (19,'dix-neuf')
59 INSERT INTO auf_tt_chiffreEnLettre values (20,'vingt')
60 INSERT INTO auf_tt_chiffreEnLettre values (30,'trente')
61 INSERT INTO auf_tt_chiffreEnLettre values (40,'quarante')
62 INSERT INTO auf_tt_chiffreEnLettre values (50,'cinquante')
63 INSERT INTO auf_tt_chiffreEnLettre values (60,'soixante')
64 INSERT INTO auf_tt_chiffreEnLettre values (70,'soixante-dix')
65 INSERT INTO auf_tt_chiffreEnLettre values (80,'quatre-vingt')
66 INSERT INTO auf_tt_chiffreEnLettre values (90,'quatre-vingt-dix')
67 */
68
69 SELECT @n = @reel
70
71 SELECT @vergule = (@reel - @n)*100
72
73 SELECT @n = @reel - @vergule
74
75 SELECT @montlettre = ''
76
77 SELECT @100mille = @n/100000
78
79 IF ( @100mille > 0)
80 BEGIN
81
82 SELECT @milleflag = 'O'
83
84 SELECT @n = @n - @100mille*100000
85
86 IF ( @100mille > 1 )
87 SELECT @montlettre = (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @100mille) + ' cent'
88 ELSE
89 SELECT @montlettre = 'cent'
90
91 END
92
93
94 SELECT @10mille = @n/1000
95
96
97 IF ( @10mille > 0)
98 BEGIN
99
100 SELECT @milleflag = 'O'
101
102 SELECT @n = @n - @10mille*1000
103
104 IF ( @10mille < 21 )
105 BEGIN
106
107 IF ( @10mille > 1 )
108 SELECT @montlettre = @montlettre + ' ' + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @10mille)
109
110 END
111 ELSE
112 BEGIN
113 SELECT @dix = @10mille/10
114
115 IF ( @dix = 7 OR @dix = 9 )
116 BEGIN
117 SELECT @m = @dix*10 - 10
118 SELECT @montlettre = @montlettre + ' ' + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @m) + '-'
119 SELECT @m = @10mille - @m
120 END
121 ELSE
122 BEGIN
123 SELECT @montlettre = @montlettre + ' ' + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @dix*10) + ' '
124 SELECT @m = @10mille - @dix*10
125 END
126
127 IF (@m > 0 )
128 SELECT @montlettre = @montlettre + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @m)
129 END
130
131 END
132
133
134 IF (@milleflag = 'O')
135 SELECT @montlettre = @montlettre +' mille'
136
137
138
139
140
141 SELECT @cent = @n/100
142
143
144 IF ( @cent > 0 AND @n > 0)
145 BEGIN
146
147 SELECT @n = @n - @cent*100
148
149 IF ( @cent > 1 )
150 SELECT @montlettre = @montlettre + ' ' + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @cent) + ' cent'
151 ELSE
152 SELECT @montlettre = @montlettre + ' cent'
153
154 END
155
156
157 IF ( @n > 0 )
158 BEGIN
159 IF ( @n < 21 )
160 BEGIN
161 SELECT @montlettre = @montlettre + ' ' + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @n)
162 END
163 ELSE
164 BEGIN
165
166 SELECT @dix = @n/10
167
168 IF ( @dix = 7 OR @dix = 9 )
169 BEGIN
170 SELECT @m = @dix*10 - 10
171 SELECT @montlettre = @montlettre + ' ' + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @m) + '-'
172 SELECT @m = @n - @m
173 END
174 ELSE
175 BEGIN
176 SELECT @montlettre = @montlettre + ' ' + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @dix*10) + ' '
177 SELECT @m = @n - @dix*10
178 END
179
180 IF (@m > 0 )
181 SELECT @montlettre = @montlettre + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @m)
182
183 END
184 END
185
186 SELECT @montlettre = @montlettre + ' ' + @devise
187
188 SELECT @n = @vergule
189
190 IF ( @n > 0)
191 BEGIN
192
193 IF ( @n < 21 )
194 BEGIN
195 SELECT @montlettre = @montlettre + ' ' + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @10mille)
196 END
197 ELSE
198 BEGIN
199
200 SELECT @dix = @n/10
201
202 IF ( @dix = 7 OR @dix = 9 )
203 BEGIN
204 SELECT @m = @dix*10 - 10
205 SELECT @montlettre = @montlettre + ' ' + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @m) + '-'
206 SELECT @m = @n - @m
207 END
208 ELSE
209 BEGIN
210 SELECT @montlettre = @montlettre + ' ' + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @dix*10) + ' '
211 SELECT @m = @n - @dix*10
212 END
213
214 IF (@m > 0 )
215 SELECT @montlettre = @montlettre + (select valeur FROM auf_tt_chiffreEnLettre WHERE code = @m)
216
217 END
218
219 SELECT @montlettre = @montlettre + ' centimes'
220
221 END
222
223 return @montlettre
224
225
226 END
227
228
229
230
231
232
233 GO