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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
| create function prixBateauPeriode(bateau int(5),db date,df date)
RETURNS int(7)
BEGIN
DECLARE total decimal(7,2) default 0;
DECLARE totalTemp decimal(7,2) default 0;
DECLARE ok int(1) default 1;
DECLARE ok2 int(1) default 1;
DECLARE tempdb date default db;
DECLARE tempdb2 date;
DECLARE prixS decimal(7,2);
DECLARE reste int(1);
DECLARE part decimal(5,2);
DECLARE breton tinyint(1);
DECLARE samedi tinyint(1);
DECLARE nbSemaines int(2);
DECLARE remise decimal(5,2) default 0;
DECLARE tauxEB1 decimal(5,2) default 0;
DECLARE tauxEB2 decimal(5,2) default 0;
DECLARE tauxEB3 decimal(5,2) default 0;
DECLARE lastMinute decimal(5,2) default 0;
DECLARE promo decimal(5,2) default 0;
DECLARE nb int(2);
DECLARE db2 date;
DECLARE df2 date;
DECLARE noSemaine1 int(2);
DECLARE noSemaine2 int(2);
DECLARE CONTINUE HANDLER FOR NOT FOUND set ok=0;
set nbSemaines=(to_days(df)-to_days(db)+1)div 7;
select count(*) into nb from bateaux where noBateau=bateau and delaiEarlyBooking1<=((to_days(db)-to_days(now()))div 7) and tauxEarlyBooking1 is not null;
if (nb!=0) then
select tauxEarlyBooking1 into tauxEB1 from bateaux where noBateau=bateau and delaiEarlyBooking1<=((to_days(db)-to_days(now()))div 7) and tauxEarlyBooking1 is not null;
end if;
select count(*) into nb from bateaux where noBateau=bateau and now()<=dateEarlyBooking2 and tauxEarlyBooking2 is not null;
if (nb!=0) then
select tauxEarlyBooking2 into tauxEB2 from bateaux where noBateau=bateau and now()<=dateEarlyBooking2 and tauxEarlyBooking2 is not null;
end if;
select count(*) into nb from bateaux where noBateau=bateau and now()<=dateEarlyBooking3 and tauxEarlyBooking3 is not null;
if (nb!=0) then
select tauxEarlyBooking3 into tauxEB3 from bateaux where noBateau=bateau and now()<=dateEarlyBooking3 and tauxEarlyBooking3 is not null;
end if;
select count(*) into nb from bateaux where noBateau=bateau and delaiDerniereMinute>=(to_days(db)-to_days(now())) and tauxDerniereMinute is not null;
if (nb!=0) then
select tauxDerniereMinute into lastMinute from bateaux where noBateau=bateau and delaiDerniereMinute>=(to_days(db)-to_days(now())) and tauxDerniereMinute is not null;
end if;
if (tauxEB1<tauxEB2) then
set tauxEB1=tauxEB2;
end if;
if (tauxEB1<tauxEB3) then
set tauxEB1=tauxEB3;
end if;
select calculBreton into breton from bateaux where noBateau=bateau;
select samediSamedi into samedi from bateaux where noBateau=bateau;
WHILE (ok=1 and tempdb<=df) DO
select dateFin,prixSemaine into tempdb2,prixS from periodesLocationsBateaux where tempdb>=dateDebut and tempdb<=dateFin and noBateau=bateau and prixSemaine is not null and prixSemaine>0 and dateDebut<=all(select dateDebut from periodesLocationsBateaux where tempdb>=dateDebut and tempdb<=dateFin and noBateau=bateau and prixSemaine is not null and prixSemaine>0);
if (ok=1) then
if (tempdb2>df) then
set tempdb2=df;
end if;
if (to_days(tempdb2)-to_days(tempdb)>6 and samedi=0) then
set totalTemp=totalTemp+((to_days(tempdb2)-to_days(tempdb)) div 7)*prixS;
set reste=((to_days(tempdb2)-to_days(tempdb)) mod 7);
else
set totalTemp=totalTemp+((to_days(tempdb2)-to_days(tempdb)+1) div 7)*prixS;
set reste=((to_days(tempdb2)-to_days(tempdb)+1) mod 7);
end if;
if (reste!=0) then
select count(*) into nb from tarifsJournaliers where noBateau=bateau and nbjours=reste;
if (nb=6) then
select partSemaine into part from tarifsJournaliers where noBateau=bateau and nbjours=reste;
else
set part=reste/7;
end if;
set totalTemp=totalTemp+prixS*part;
end if;
if (breton!=1) then
if (nbSemaines >1) then
select count(*) into nb from reductions where tempdb>=dateDebut and tempdb<dateFin and noBateau=bateau and noSemaine<=nbSemaines and noSemaine>=all(select noSemaine from reductions where tempdb between dateDebut and dateFin and noBateau=bateau and noSemaine<=nbSemaines);
if (nb>0) then
select pourcentageRemise into remise from reductions where tempdb>=dateDebut and tempdb<dateFin and noBateau=bateau and noSemaine<=nbSemaines and noSemaine>=all(select noSemaine from reductions where tempdb between dateDebut and dateFin and noBateau=bateau and noSemaine<=nbSemaines);
else
set remise=0;
end if;
end if;
set totalTemp=totalTemp*(100-remise)/100;
else
if (nbSemaines >1) then
set db2=tempdb;
set df2=date_add(db2,interval 7 day);
WHILE (to_days(df2)-to_days(tempdb2)<7 and to_days(df2)-to_days(db2)!=0) DO
if (df2>tempdb2) then
set df2=tempdb2;
end if;
if (to_days(df2)-to_days(db2)!=0) then
if ((to_days(db2)-to_days(db)) mod 7 !=0 and (to_days(df2)-to_days(tempdb))=7) then
set df2=date_add(df2,interval -((to_days(db2)-to_days(db)) mod 7) day);
end if;
if (((to_days(df2)-to_days(db)) div 7)>1) then
select pourcentageRemise into remise from reductions where df2>=dateDebut and df2<dateFin and noBateau=bateau and noSemaine<=((to_days(df2)-to_days(db)+1) div 7)+1 and noSemaine>=all(select noSemaine from reductions where df2 between dateDebut and dateFin and noBateau=bateau and noSemaine<=((to_days(df2)-to_days(db)+1) div 7)+1);
set nb=to_days(df2)-to_days(db2)+1;
if (nb<7) then
select partSemaine into part from tarifsJournaliers where noBateau=bateau and nbjours=nb mod 7;
set totalTemp=totalTemp-(part*prixS*(remise)/100);
set db2=df2;
set df2=date_add(df2,interval nb mod 7 day);
else
set totalTemp=totalTemp-(prixS*(remise)/100);
set db2=df2;
set df2=date_add(df2,interval 7 day);
end if;
else
set db2=df2;
set df2=date_add(df2,interval 7 day);
end if;
end if;
END WHILE;
end if;
end if;
set totalTemp=totalTemp*(100-tauxEB1)/100;
set totalTemp=totalTemp*(100-lastMinute)/100;
WHILE (ok2=1 and tempdB<=tempdb2) DO
select count(*) into nb from promotionsBateaux where noBateau=bateau and dateDebut<tempdb2 and dateFin>=tempdb;
if (nb>0) then
select tauxPromotion,dateDebut,dateFin into promo,db2,df2 from promotionsBateaux where noBateau=bateau and dateDebut<tempdb2 and dateFin>=tempdb and dateFin<=all(select dateFin from promotionsBateaux where dateDebut<=tempdb2 and dateFin>=tempdb);
if (db2<tempdb) then
set db2=tempdb;
end if;
if (df2>tempdb2) then
set df2=tempdb2;
end if;
set totalTemp=totalTemp-totalTemp*((to_days(df2)-to_days(db2)+1)/(to_days(tempdb2)-to_days(tempdb)+1))*promo/100;
else
set ok2=0;
end if;
set tempdb=date_add(df2,interval '1' day);
END WHILE;
set total=total+totalTemp;
set totalTemp=0;
set tempdb2=date_add(tempdb2,interval '1' day);
set tempdb=tempdb2;
end if;
set @db=tempdb;
END WHILE;
set total=total-0.50;
select prixMaxBateauPeriode(bateau,db,df) into totalTemp;
select tauxRemiseMax into remise from bateaux b,affreteurs a where b.noAffreteur=a.noAffreteur and noBateau=bateau;
if (totalTemp*(100-remise)/100<total) then
set total=totalTemp*(100-remise)/100;
end if;
return total;
END |
