1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
CREATE PROCEDURE test.tab_quotiten( IN nb_echeance INT,
IN mois INT, IN annee INT, IN capital INT, IN taux FLOAT,
IN ass FLOAT, IN Q1 FLOAT, IN Q2 FLOAT, IN Q3 FLOAT, IN Q4 FLOAT, IN Q5 FLOAT, IN Q6 FLOAT, IN Q7 FLOAT, IN Q8 FLOAT,
IN Q9 FLOAT, IN Q10 FLOAT, IN Q11 FLOAT, IN Q12 FLOAT, IN Q13 FLOAT, IN Q14 FLOAT, IN Q15 FLOAT, IN Q16 FLOAT,
IN Q17 FLOAT, IN Q18 FLOAT, IN Q19 FLOAT, IN Q20 FLOAT, IN Q21 FLOAT, IN Q22 FLOAT, IN Q23 FLOAT, IN Q24 FLOAT, IN Q25 FLOAT)
BEGIN
DECLARE id INT DEFAULT 0;
DECLARE nb_mois INT DEFAULT 1;
DECLARE i FLOAT ;
DECLARE j INT DEFAULT 0 ;
DECLARE a FLOAT;
DECLARE Echeance FLOAT;
DECLARE x, y, b FLOAT;
DECLARE Crd FLOAT;
DECLARE Interet FLOAT;
DECLARE capital_amort FLOAT;
DECLARE echean_totale FLOAT;
DECLARE Interet_Infine FLOAT ;
DECLARE capital_infine FLOAT ;
DECLARE Q0 FLOAT ;
SET Crd = capital;
SET Q0 = Q1;
WHILE nb_mois <= nb_echeance DO
SET i = ((nb_mois - 1 ) / 12);
SET j = TRUNCATE(i,0);
IF ((i != 0) && (j = i))
THEN
SET Q0 = Q1 ;
SET Q1 = Q2; SET Q2 = Q3; SET Q3 = Q4; SET Q4 = Q5;
SET Q5 = Q6; SET Q6 = Q7; SET Q7 = Q8; SET Q8 = Q9;
SET Q9 = Q10; SET Q10 = Q11; SET Q11 = Q12; SET Q12 = Q13;
SET Q13 = Q14; SET Q14 = Q15; SET Q15 = Q16; SET Q16 = Q17;
SET Q17 = Q18; SET Q18 = Q19; SET Q19 = Q20; SET Q20 = Q21;
SET Q21 = Q22; SET Q22 = Q23; SET Q23 = Q24; SET Q24 = Q25;
END IF;
IF (( i = 0 || Q1 != Q0) && ( j = i))
THEN
SET capital_infine = (Crd *( 1 - Q1));
SET capital_amort = Crd - capital_infine ;
-- Calcul de l-echeance amortissable
SET x = (capital_amort * (taux /12 ));
SET y = pow(( 1 + (taux /12)), - (nb_echeance - nb_mois + 1 ));
SET Echeance = x / ( 1 - y );
-- Calcul des interet pour la part Infine
SET Interet_Infine = ((capital_infine) * (taux / 12 ));
END IF;
SET a = Crd - capital_infine;
SET b = a * (taux /12);
SET capital_amort = Echeance - b;
SET echean_totale = Interet_infine + Echeance + ass ;
SET Crd = Crd - capital_amort;
INSERT INTO test.tab_quotiten (`id`,`nb`, `i`,`mois`, `annee`, `Quotite`,`interet`,`capital_amortit`,`echeance`,`assurance`,`echeance_totale`, `interet_infine`, `KInfine`, `CRD`)
VALUES (`id`,`nb_mois`, `i`,`mois`, `annee`,`Q1`,`b`,`capital_amort`,`Echeance`,`ass`,`echean_totale`,`Interet_Infine`,`capital_infine`, `Crd`) ;
IF mois = 12 THEN
SET mois = 1;
SET annee = annee + 1;
ELSE
SET mois = mois + 1 ;
END IF;
SET nb_mois = nb_mois + 1 ;
END WHILE ;
END $$ |
procédures stockées et tableau
Répondre avec citation
Envoyé par deborah95
Partager