Nouvelle catégorie "Charges cotisations" dans l'état des résultats
[auf_coda_additions.git] / db / fonctions / auf_f_montantenLettre.sql
CommitLineData
22ab1788
EMS
1USE [coda-prod]
2GO
3
4DROP FUNCTION [dbo].[auf_f_montantenLettre]
5GO
6
7SET ANSI_NULLS ON
8GO
9SET QUOTED_IDENTIFIER ON
10GO
11
12
13--SELECT dbo.auf_f_montantenLettre( 2345,' CAD')
14
15
16CREATE FUNCTION [dbo].[auf_f_montantenLettre](@reel decimal(19,4), @devise varchar(12) ) returns varchar(150)
17
18AS
19
20BEGIN
21
22DECLARE @n AS int
23DECLARE @val AS varchar(100)
24DECLARE @100mille AS int
25DECLARE @10mille AS int
26DECLARE @mille AS int
27DECLARE @cent AS int
28DECLARE @dix AS int
29DECLARE @montlettre varchar(100)
30DECLARE @vergule int
31DECLARE @milleflag varchar(1)
32DECLARE @m AS int
33
34/*
35create table auf_tt_chiffreEnLettre (
36code int,
37valeur varchar(30)
38)
39
40INSERT INTO auf_tt_chiffreEnLettre values (1,'un')
41INSERT INTO auf_tt_chiffreEnLettre values (2,'deux')
42INSERT INTO auf_tt_chiffreEnLettre values (3,'trois')
43INSERT INTO auf_tt_chiffreEnLettre values (4,'quatre')
44INSERT INTO auf_tt_chiffreEnLettre values (5,'cinq')
45INSERT INTO auf_tt_chiffreEnLettre values (6,'six')
46INSERT INTO auf_tt_chiffreEnLettre values (7,'sept')
47INSERT INTO auf_tt_chiffreEnLettre values (8,'huit')
48INSERT INTO auf_tt_chiffreEnLettre values (9,'neuf')
49INSERT INTO auf_tt_chiffreEnLettre values (10,'dix')
50INSERT INTO auf_tt_chiffreEnLettre values (11,'onze')
51INSERT INTO auf_tt_chiffreEnLettre values (12,'douze')
52INSERT INTO auf_tt_chiffreEnLettre values (13,'treize')
53INSERT INTO auf_tt_chiffreEnLettre values (14,'quatorze')
54INSERT INTO auf_tt_chiffreEnLettre values (15,'quinze')
55INSERT INTO auf_tt_chiffreEnLettre values (16,'seize')
56INSERT INTO auf_tt_chiffreEnLettre values (17,'dix-sept')
57INSERT INTO auf_tt_chiffreEnLettre values (18,'dix-huit')
58INSERT INTO auf_tt_chiffreEnLettre values (19,'dix-neuf')
59INSERT INTO auf_tt_chiffreEnLettre values (20,'vingt')
60INSERT INTO auf_tt_chiffreEnLettre values (30,'trente')
61INSERT INTO auf_tt_chiffreEnLettre values (40,'quarante')
62INSERT INTO auf_tt_chiffreEnLettre values (50,'cinquante')
63INSERT INTO auf_tt_chiffreEnLettre values (60,'soixante')
64INSERT INTO auf_tt_chiffreEnLettre values (70,'soixante-dix')
65INSERT INTO auf_tt_chiffreEnLettre values (80,'quatre-vingt')
66INSERT INTO auf_tt_chiffreEnLettre values (90,'quatre-vingt-dix')
67*/
68
69SELECT @n = @reel
70
71SELECT @vergule = (@reel - @n)*100
72
73SELECT @n = @reel - @vergule
74
75SELECT @montlettre = ''
76
77SELECT @100mille = @n/100000
78
79IF ( @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
233GO