Traitement des déplacements

**mouhiiddine** · 08/05/2016, 19h48

Bonjour tout le monde,

j'aimerai bien demander votre aide pour résoudre mon problème avec le calcule de nombre de taux de déplacement pour une période

sachant que pour une journée de déplacement les régles de calcule de taux est comme suite:
du 00h00 _____ 05h00 =1 tau
du 11h00 _____14h00 = 1 tau
du 18h00______21h00 =1 tau
pour avoir 3 taux dans une journée il faut que l'heure départ soit avant ou égale 00h00 et heure retour après ou égale 21h00 pour un jour bien sûre
si l'heure de départ ou de retoure est entre 00h00 et 05h00 ou 11h00 et 14h00 ou 16h00 et 21h00 ke taux nes pas compter.
exemples

N° date/heure départ date/heure retoure Villle Taux déplacement
1 2016-05-01 00:00:00 2016-05-01 00:00:00 aze 0
4 2016-05-02 04:04:00 2016-05-04 20:00:00 casa 7
5 2016-05-05 00:00:00 2016-05-05 20:11:00 azer 2
6 2016-05-06 00:00:00 2016-05-06 23:59:59 azer 3
7 2016-05-07 00:59:00 2016-05-08 23:59:00 azer
8 2016-05-10 00:00:00 2016-05-11 23:59:00 casa
9 2016-05-12 00:00:00 2016-05-14 00:00:00 azer
10 2016-05-29 11:00:00 2016-05-29 17:59:00 azer
11 2016-05-15 00:00:00 2016-05-15 21:30:00 jak
12 2016-05-28 00:00:00 2016-05-28 14:00:00 biougra

j’espère que j'été claire dans mes explications

j'aimerai bien créer une requête avec mysql pour faire ce traitement.

merci infiniment pour votre aide

**Artemus24** · 09/05/2016, 02h18

Salut mouhiiddine.

Pour résoudre ton problème, j'ai procédé à deux changements dans ta table :

1) j'ai séparé la date de l'heure en créant deux colonnes : time_depart et time_retour.
De cela, on peut utiliser un index afin d'améliorer les performances.
Si la colonne time_retour doit se terminer à minuit, j'ai codifié cela en mettant '24:00:00', indiquant la fin de la journée.
Inversement, pour la colonne time_depart, le début de la journée commence à '00:00:00'.

2) j'ai considéré qu'une ligne est l'équivalent d'une journée.
De ce fait, comme la ligne représente une journée, il n'est plus nécessaire d'avoir date_depart et date_retour.
J'ai mis une seule date qui se nomme tout simplement date.
Une période se répartissant sur trois jours, donnera trois lignes dans la table.
C'est cette astuce qui permet de calculer correctement les trois intervalles pour chaque jour donné.

Voici la solution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
(
  `id`            integer  unsigned NOT NULL AUTO_INCREMENT primary key,
  `id_perso`      integer  unsigned NOT NULL,
  `date`          date              NOT NULL,
  `time_depart`   time              NOT NULL,
  `time_retour`   time              NOT NULL,
  `ville`         varchar(255)      NOT NULL,
  index `idx` (`time_depart`,`time_retour`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`id_perso`,`date`,`time_depart`,`time_retour`,`ville`) VALUES
  ( 1, '2016-05-01', '00:00:00', '00:00:00', 'bou azer'),
  ( 4, '2016-05-02', '04:04:00', '24:00:00', 'casablanca'),
  ( 4, '2016-05-03', '00:00:00', '24:00:00', 'casablanca'),
  ( 4, '2016-05-04', '00:00:00', '20:00:00', 'casablanca'),
  ( 5, '2016-05-05', '00:00:00', '20:11:00', 'bou azer'),
  ( 6, '2016-05-06', '00:00:00', '24:00:00', 'bou azer'),
  ( 7, '2016-05-07', '00:59:00', '24:00:00', 'bou azer'),
  ( 7, '2016-05-08', '00:00:00', '23:59:00', 'bou azer'),
  ( 8, '2016-05-10', '00:00:00', '24:00:00', 'casablanca'),
  ( 8, '2016-05-11', '00:00:00', '23:59:00', 'casablanca'),
  ( 9, '2016-05-12', '00:00:00', '24:00:00', 'bou azer'),
  ( 9, '2016-05-13', '00:00:00', '24:00:00', 'bou azer'),
  (10, '2016-05-29', '11:00:00', '17:59:00', 'bou azer'),
  (11, '2016-05-15', '00:00:00', '21:30:00', 'marrakech'),
  (12, '2016-05-28', '00:00:00', '14:00:00', 'biougra')
--------------
 
--------------
select * from test
--------------
 
+----+----------+------------+-------------+-------------+------------+
| id | id_perso | date       | time_depart | time_retour | ville      |
+----+----------+------------+-------------+-------------+------------+
|  1 |        1 | 2016-05-01 | 00:00:00    | 00:00:00    | bou azer   |
|  2 |        4 | 2016-05-02 | 04:04:00    | 24:00:00    | casablanca |
|  3 |        4 | 2016-05-03 | 00:00:00    | 24:00:00    | casablanca |
|  4 |        4 | 2016-05-04 | 00:00:00    | 20:00:00    | casablanca |
|  5 |        5 | 2016-05-05 | 00:00:00    | 20:11:00    | bou azer   |
|  6 |        6 | 2016-05-06 | 00:00:00    | 24:00:00    | bou azer   |
|  7 |        7 | 2016-05-07 | 00:59:00    | 24:00:00    | bou azer   |
|  8 |        7 | 2016-05-08 | 00:00:00    | 23:59:00    | bou azer   |
|  9 |        8 | 2016-05-10 | 00:00:00    | 24:00:00    | casablanca |
| 10 |        8 | 2016-05-11 | 00:00:00    | 23:59:00    | casablanca |
| 11 |        9 | 2016-05-12 | 00:00:00    | 24:00:00    | bou azer   |
| 12 |        9 | 2016-05-13 | 00:00:00    | 24:00:00    | bou azer   |
| 13 |       10 | 2016-05-29 | 11:00:00    | 17:59:00    | bou azer   |
| 14 |       11 | 2016-05-15 | 00:00:00    | 21:30:00    | marrakech  |
| 15 |       12 | 2016-05-28 | 00:00:00    | 14:00:00    | biougra    |
+----+----------+------------+-------------+-------------+------------+
--------------
select id_perso, sum(taux) as taux
from (
    select id_perso, count(*) as taux from test where time_depart <= '00:00:00' and time_retour >= '05:00:00' group by id_perso
    union all
    select id_perso, count(*) as taux from test where time_depart <= '11:00:00' and time_retour >= '14:00:00' group by id_perso
    union all
    select id_perso, count(*) as taux from test where time_depart <= '18:00:00' and time_retour >= '21:00:00' group by id_perso
) as x
group by id_perso
--------------
 
+----------+------+
| id_perso | taux |
+----------+------+
|        4 |    7 |
|        5 |    2 |
|        6 |    3 |
|        7 |    5 |
|        8 |    6 |
|        9 |    6 |
|       10 |    1 |
|       11 |    3 |
|       12 |    2 |
+----------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

**escartefigue** · 09/05/2016, 08h56

Envoyé par Artemus24

Pour résoudre ton problème, j'ai procédé à deux changements dans ta table :
1) j'ai séparé la date de l'heure en créant deux colonnes : time_depart et time_retour.

Bonjour,

Cette solution ne convient pas, car il y a des cas où le départ et l'arrivée ne se font pas le même jour, exemple les lignes 4 et 9 du jeu d'essais.
Une solution est certainement possible sans modifier la description de la table, mais j'avoue ne pas comprendre la description du besoin :
pourquoi la ligne 4 vaut elle 7 taux alors qu'il y a 1 journée pleine (3 tau) et 2 journées partielles (à 1) si j'ai bien compris l'énoncé, ça devrait faire 5

**mouhiiddine** · 09/05/2016, 09h55

Bonjour artemus24
Pour ta reponse ne convient pas à mon probléme parce que dans mon cas j'ai une date depart avec heure depart et une date retour avec heure retour en plus je ne dois pas grouper par personne parce que moi je cherche avoir nombre taux par deplacement
C a d du date depart/heure depart au date retour/heure retour

Merci pour votre aide

**Artemus24** · 09/05/2016, 16h49

Salut à tous.

@ Escartefigue : la solution convient très bien car une ligne dans la table correspond à un jour.
Je reprends la ligne 4 donné par "mouhiiddine", que voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
+----+---------------------+---------------------+--------+------+-------------+
| N° |  date/heure départ  |  date/heure retoure | Villle | Taux | déplacement |
+----+---------------------+---------------------+--------+------+-------------+
| 4  | 2016-05-02 04:04:00 | 2016-05-04 20:00:00 |  casa  |  7   |             |
+----+---------------------+---------------------+--------+------+-------------+

je transforme cette ligne en trois nouvelle lignes dans la nouvelle table, une par jour :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
+----+---------------+--------------+--------------+-------+
| N° |  date départ  | heure départ | heure retour | ville |
+----+---------------+--------------+--------------+-------+
| 4  | 2016-05-02    |   04:04:00   |   24:00:00   |  casa |
| 4  | 2016-05-03    |   00:00:00   |   24:00:00   |  casa |
| 4  | 2016-05-04    |   00:00:00   |   20:00:00   |  casa |
+----+---------------+--------------+--------------+-------+

Envoyé par Escartefigue

pourquoi la ligne 4 vaut elle 7 taux alors qu'il y a 1 journée pleine (3 tau) et 2 journées partielles (à 1) si j'ai bien compris l'énoncé, ça devrait faire 5

La ligne 4 comprend trois jours. Il faut décomposer le calcul jour par jour.

En faisant le calcul j'obtiens :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
--> première ligne  : de 11h00 à 14h00 et                     de 18h00 à 21h00 soit 2 taux.
--> deuxième ligne  : de 00h00 à 05h00,   de 11h00 à 14h00 et de 18h00 à 21h00 soit 3 taux.
--> troisième ligne : de 00h00 à 05h00 et de 11h00 à 14h00                     soit 2 taux.
total : 2 + 3 + 2 = 7 taux.

A moins de me tromper dans le calcul, je pense que c'est le taux attendu par mouhiiddine.

Envoyé par Escartefigue

Une solution est certainement possible sans modifier la description de la table

A vrai dire, je n'ai pas trouvé d'autres solutions, sans que cela devienne une usine à gaz.
Je n'ai pas voulu passer par une procédure stockée, ce qui aurait "peut-être" pu donner la solution recherchée.
Car le principale problème est de calculer le taux sur des périodes à cheval sur plusieurs jours.
C'est pourquoi, la solution la plus simple (à mon avis), est de mettre un jour par ligne.

Envoyé par mouhiiddine

Pour ta reponse ne convient pas à mon probléme parce que dans mon cas j'ai une date depart avec heure depart et une date retour avec heure retour ...

Oui, je suis bien conscient que j'ai dû changer la structure de ta table pour résoudre le problème.

Si mettre un jour par ligne ne te convient pas, je n'ai pas de solution simple à te soumettre.

Envoyé par mouhiiddine

en plus je ne dois pas grouper par personne parce que moi je cherche avoir nombre taux par deplacement C a d du date depart/heure depart au date retour/heure retour

Je pense que tu as survolé la solution sans regarder ce que j'ai fait de plus près.

Est-ce que le calcul du taux est correcte pour le tableau suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
+----+---------------------+---------------------+---------+------+-------------+
| N° |  date/heure départ  |  date/heure retoure |  Ville  | Taux | déplacement |
+----+---------------------+---------------------+---------+------+-------------+
|  1 | 2016-05-01 00:00:00 | 2016-05-01 00:00:00 | aze     |   0  |             |
|  4 | 2016-05-02 04:04:00 | 2016-05-04 20:00:00 | casa    |   7  |             |
|  5 | 2016-05-05 00:00:00 | 2016-05-05 20:11:00 | azer    |   2  |             |
|  6 | 2016-05-06 00:00:00 | 2016-05-06 23:59:59 | azer    |   3  |             |
|  7 | 2016-05-07 00:59:00 | 2016-05-08 23:59:00 | azer    |   5  |             |
|  8 | 2016-05-10 00:00:00 | 2016-05-11 23:59:00 | casa    |   6  |             |
|  9 | 2016-05-12 00:00:00 | 2016-05-14 00:00:00 | azer    |   6  |             |
| 10 | 2016-05-29 11:00:00 | 2016-05-29 17:59:00 | azer    |   1  |             |
| 11 | 2016-05-15 00:00:00 | 2016-05-15 21:30:00 | jak     |   3  |             |
| 12 | 2016-05-28 00:00:00 | 2016-05-28 14:00:00 | biougra |   2  |             |
+----+---------------------+---------------------+---------+------+-------------+

Si c'est oui alors j'ai répondu à ton problème.
Le N° dans ton tableau est ce que j'ai nommé "id_perso. Peux-être est-ce une erreur de ma part d'avoir nommé cette colonne ainsi.
Ce tableau est ton exemple que tu as donné dans ton premier message, que j'ai complété (ce qui est en rouge) par le résultat qui vient de la requête de mon exemple.

De plus, tu as bien formulé le désire suivant :

Envoyé par mouhiiddine

j'aimerai bien créer une requête avec mysql pour faire ce traitement.

et c'est bien une requête que je t'ai donné.

Envoyé par mouhiiddine

Merci pour votre aide

Nous sommes là pour ça.

@+

**mouhiiddine** · 09/05/2016, 21h24

Rebonjour Artemus24
Merci pour tes explications j'ai bien compris ta sollution
Mais comment faire pour repartir une periode par exemple de3 jours dont date depart par exemple 02/5/2016 au 4/5/2016 pour arriver a ta sollution sachont que mon formulaire de saisi j'ai seulement date depart heure depart et date retour heure retour c ad quand je saisie un deplacement je fais entrer toute une periode et pas le jour par jour.
Merci encore une fois

**Artemus24** · 11/05/2016, 11h49

Salut mouhiiddine.

Désolé pour hier, je n'étais pas disponible.

Voici un autre solution, en passant cette fois-ci par une fonction (et oui, ce n'est plus une requête select).
Je pense que cela doit correspondre à ce que tu attends.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`            integer  unsigned NOT NULL AUTO_INCREMENT primary key,
  `id_perso`      integer  unsigned NOT NULL,
  `date_depart`   datetime          NOT NULL,
  `date_retour`   datetime          NOT NULL,
  `ville`         varchar(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
CREATE FUNCTION `taux`(retour datetime,
                       depart datetime)
RETURNS smallint unsigned
DETERMINISTIC
LANGUAGE SQL
BEGIN
  DECLARE _calc      smallint UNSIGNED DEFAULT 0;
  DECLARE _date      date              DEFAULT date(depart);
  DECLARE _time_deb  time;
  DECLARE _time_fin  time;
 
  WHILE (_date <= date(retour)) DO
    IF (_date = date(depart)) THEN SET _time_deb = time(depart); ELSE SET _time_deb = '00:00:00'; END IF;
    IF (_date = date(retour)) THEN SET _time_fin = time(retour); ELSE SET _time_fin = '24:00:00'; END IF;
 
    IF ((_time_deb <= '00:00:00') AND (_time_fin >= '05:00:00')) THEN SET _calc = _calc + 1; END IF;
    IF ((_time_deb <= '11:00:00') AND (_time_fin >= '14:00:00')) THEN SET _calc = _calc + 1; END IF;
    IF ((_time_deb <= '18:00:00') AND (_time_fin >= '21:00:00')) THEN SET _calc = _calc + 1; END IF;
 
    SET _date = date_add(_date, interval 1 day);
  END WHILE;
  RETURN _calc;
END
--------------
 
--------------
INSERT INTO `test` (`id_perso`,`date_depart`,`date_retour`,`ville`) VALUES
  ( 1, '2016-05-01 00:00:00', '2016-05-01 00:00:00', 'bou azer'),
  ( 4, '2016-05-02 04:04:00', '2016-05-04 20:00:00', 'casablanca'),
  ( 5, '2016-05-05 00:00:00', '2016-05-05 20:11:00', 'bou azer'),
  ( 6, '2016-05-06 00:00:00', '2016-05-06 23:59:59', 'bou azer'),
  ( 7, '2016-05-07 00:59:00', '2016-05-08 23:59:00', 'bou azer'),
  ( 8, '2016-05-10 00:00:00', '2016-05-11 23:59:00', 'casablanca'),
  ( 9, '2016-05-12 00:00:00', '2016-05-14 00:00:00', 'bou azer'),
  (10, '2016-05-29 11:00:00', '2016-05-29 17:59:00', 'bou azer'),
  (11, '2016-05-15 00:00:00', '2016-05-15 21:30:00', 'marrakech'),
  (12, '2016-05-28 00:00:00', '2016-05-28 14:00:00', 'biougra')
--------------
 
--------------
select * from test
--------------
 
+----+----------+---------------------+---------------------+------------+
| id | id_perso | date_depart         | date_retour         | ville      |
+----+----------+---------------------+---------------------+------------+
|  1 |        1 | 2016-05-01 00:00:00 | 2016-05-01 00:00:00 | bou azer   |
|  2 |        4 | 2016-05-02 04:04:00 | 2016-05-04 20:00:00 | casablanca |
|  3 |        5 | 2016-05-05 00:00:00 | 2016-05-05 20:11:00 | bou azer   |
|  4 |        6 | 2016-05-06 00:00:00 | 2016-05-06 23:59:59 | bou azer   |
|  5 |        7 | 2016-05-07 00:59:00 | 2016-05-08 23:59:00 | bou azer   |
|  6 |        8 | 2016-05-10 00:00:00 | 2016-05-11 23:59:00 | casablanca |
|  7 |        9 | 2016-05-12 00:00:00 | 2016-05-14 00:00:00 | bou azer   |
|  8 |       10 | 2016-05-29 11:00:00 | 2016-05-29 17:59:00 | bou azer   |
|  9 |       11 | 2016-05-15 00:00:00 | 2016-05-15 21:30:00 | marrakech  |
| 10 |       12 | 2016-05-28 00:00:00 | 2016-05-28 14:00:00 | biougra    |
+----+----------+---------------------+---------------------+------------+
--------------
select id_perso, taux(date_retour, date_depart) as taux
from test
--------------
 
+----------+------+
| id_perso | taux |
+----------+------+
|        1 |    0 |
|        4 |    7 |
|        5 |    2 |
|        6 |    3 |
|        7 |    5 |
|        8 |    6 |
|        9 |    6 |
|       10 |    1 |
|       11 |    3 |
|       12 |    2 |
+----------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Tu remarques que ce sont les mêmes résultats qu'avec l'autre approche.
Tu me dis si cela te convient ou pas !

@+

**mouhiiddine** · 12/05/2016, 01h13

Bonjour Artemus24

Merci bien pour ton aide vraiment tu es un génie

j'ai bien compris ta solution et j'ai adapté votre réponse avec mon problème puisque je développe mon site avec l'outil dreamweaver et j'utilise easyphp comme solution de serveur et gestionnaire de base de donnée.
j'ai chercher un peux pour comprendre les fonctions et comment les crées avec easyphp enfin j'ai réussi grâce à ta fonction à résoudre ce problème.

je vous remercie encore une fois ainsi toute l’équipe de developpez.net

vous êtes les meilleurs

**escartefigue** · 12/05/2016, 11h54

En effet j n'avais pas vu la duplication des lignes pour les intervalles de dates

Je valide donc

**Artemus24** · 12/05/2016, 12h42

Salut à tous.

@ Mouhiiddine : merci pour les compliments !

La fonction reprend exactement la solution que j'avais proposé, dans mon premier message.
A la différence que je ne stocke pas une ligne par jour dans la table, mais que je crée une boucle pour reproduire le même comportement.
J'espère que les tests ont été concluant !

Si vous êtes satisfait alors je le suis aussi.

@ Escartefigue : oui, il y a bien une solution sans modifier la table.
Mais ce qui ne me plait pas du tout, c'est de faire une boucle pour déterminer les intervalles de temps.
J'ai peut être exagéré en disant que cela devenait une usine à gaz, mais imagine de faire passer la fonction sur plusieurs million de lignes.
Ça va ramer et il est impossible d'améliorer la fonction pour éviter ce genre de désagrément.

Merci pour le +1 !

@+

**mouhiiddine** · 14/05/2016, 01h03

bonjour Artemus24

merci encore une fois.
j'ai encore une autre problème,après calcule des taux j'applique une condition pour calculer le montant du déplacement

N°deplacement Date/Heure Départ Date/Heure Retour Ville Nombre Taux Montant en DH
1 2016-05-01 00:00:00 2016-05-01 00:00:00 aze 0 0
4 2016-05-02 04:04:00 2016-05-04 20:00:00 casa 7 560
5 2016-05-05 00:00:00 2016-05-05 20:11:00 azer 2 160
6 2016-05-06 00:00:00 2016-05-06 23:59:59 azer 3 240

en utilisant cette requête dont vous allez trouver les conditions de calcule

SELECT *, taux(retour, depart) as taux,(case groupe when 'Groupe1' then taux(retour, depart)*100 when 'Groupe2' then taux(retour, depart)*80 when 'Groupe3' then taux(retour, depart)*60 when 'Groupe4' then taux(retour, depart)*40 else taux(retour, depart)*30 end) as montant
FROM a.

mon problème c'est que les prix des taux se changent à compter de 16éme jours dans le cas ou la période dépasse 15 jours.

alors comment je peux résoudre ce problème dans ma requête .

merci pour votre aide encore une fois.

**Artemus24** · 14/05/2016, 10h04

Salut

Quelque chose dans ce genre là :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`            integer  unsigned NOT NULL AUTO_INCREMENT primary key,
  `id_perso`      integer  unsigned NOT NULL,
  `date_depart`   datetime          NOT NULL,
  `date_retour`   datetime          NOT NULL,
  `ville`         varchar(255)      NOT NULL,
  `taux`          smallint unsigned     NULL DEFAULT NULL,
  `montant`       decimal(15,2)         NULL DEFAULT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
CREATE FUNCTION `taux` (retour datetime,
                        depart datetime)
RETURNS smallint unsigned
DETERMINISTIC
LANGUAGE SQL
BEGIN
  DECLARE _calc      smallint UNSIGNED DEFAULT 0;
  DECLARE _date      date              DEFAULT date(depart);
  DECLARE _time_deb  time;
  DECLARE _time_fin  time;
 
  WHILE (_date <= date(retour)) DO
    IF (_date = date(depart)) THEN SET _time_deb = time(depart); ELSE SET _time_deb = '00:00:00'; END IF;
    IF (_date = date(retour)) THEN SET _time_fin = time(retour); ELSE SET _time_fin = '24:00:00'; END IF;
 
    IF ((_time_deb <= '00:00:00') AND (_time_fin >= '05:00:00')) THEN SET _calc = _calc + 1; END IF;
    IF ((_time_deb <= '11:00:00') AND (_time_fin >= '14:00:00')) THEN SET _calc = _calc + 1; END IF;
    IF ((_time_deb <= '18:00:00') AND (_time_fin >= '21:00:00')) THEN SET _calc = _calc + 1; END IF;
 
    SET _date = date_add(_date, interval 1 day);
  END WHILE;
  RETURN _calc;
END
--------------
 
--------------
CREATE FUNCTION `tarif` (retour datetime,
                         depart datetime,
                         groupe varchar(255))
RETURNS smallint unsigned
DETERMINISTIC
LANGUAGE SQL
BEGIN
  DECLARE _duree smallint UNSIGNED DEFAULT 0;
  DECLARE _prix  decimal(15,2)     DEFAULT 0;
 
  SET _duree = datediff(retour, depart);
 
  SET _prix  = case when _duree <= 15 then (case groupe when 'groupe1' then 100 when 'groupe2' then  80 when 'groupe3' then  60 when 'groupe4' then  40 else  30 end)
                    when _duree <= 22 then (case groupe when 'groupe1' then  95 when 'groupe2' then  75 when 'groupe3' then  55 when 'groupe4' then  35 else  25 end)
                    else                   (case groupe when 'groupe1' then  92 when 'groupe2' then  72 when 'groupe3' then  52 when 'groupe4' then  32 else  22 end)
                    end;
  RETURN _prix;
END
--------------
 
--------------
INSERT INTO `test` (`id_perso`,`date_depart`,`date_retour`,`ville`) VALUES
  ( 4, '2016-05-02 04:04:00', '2016-05-04 20:00:00', 'casablanca'),
  ( 7, '2016-05-07 00:59:00', '2016-05-23 23:59:00', 'bou azer'),
  ( 9, '2016-05-12 00:00:00', '2016-06-12 00:00:00', 'biougra')
--------------
 
--------------
update `test` set taux    =         taux(date_retour, date_depart)
--------------
 
--------------
update `test` set montant = taux * tarif(date_retour, date_depart, 'groupe2')
--------------
 
--------------
select *,
       datediff(date_retour, date_depart)    as durée,
       cast(montant / taux as decimal(15,2)) as prix
from test
--------------
 
+----+----------+---------------------+---------------------+------------+------+---------+-------+-------+
| id | id_perso | date_depart         | date_retour         | ville      | taux | montant | durée | prix  |
+----+----------+---------------------+---------------------+------------+------+---------+-------+-------+
|  1 |        4 | 2016-05-02 04:04:00 | 2016-05-04 20:00:00 | casablanca |    7 |  560.00 |     2 | 80.00 |
|  2 |        7 | 2016-05-07 00:59:00 | 2016-05-23 23:59:00 | bou azer   |   50 | 3750.00 |    16 | 75.00 |
|  3 |        9 | 2016-05-12 00:00:00 | 2016-06-12 00:00:00 | biougra    |   93 | 6696.00 |    31 | 72.00 |
+----+----------+---------------------+---------------------+------------+------+---------+-------+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Dans mon exemple, j'ai créé une fonction en appliquant trois barèmes des tarifs.
J'ai repris ton barème en exemple, puis j'ai appliqué un autre barème au-delà de 15 jours mais en deçà de 22 jours compris.
Et enfin, un dernier barème au-delà des 22 jours.

J'ai créé un jeu d'essai avec trois exemples.
Le vidage de la table donne aussi le calcul de la durée en jours, et du prix unitaire.

Refais des tests afin de vérifier que c'est bien ce que tu attends.
Vérifie aussi le calcul du nombre de jours !

@+

**mouhiiddine** · 15/05/2016, 20h49

Salut
Je crois j ai pas bien explique mon probleme moi je veux que ma requete applique deux prix pour meme periode cad dans la meme periode s'il depasse 15 jours a partir dr 16eme jour change le prix du taux exemple
Une periode de 1/1/2016 00:00 au 20/1/2016 23:00 je veux que ma requete calcule a la base de prix 80 dh pour les taux des 15 jours premiers apres 75 dh pour le reste de jour dans meme priode alors 3 taux par jour *80 pour le 15 jours premier +3taux par jour *75 pour le reste de la periode ce qui donne (3*15)*80+(3*5)*75 pour toute la periode.
Je crois que c'est bien expliqué.
Merci

**Artemus24** · 16/05/2016, 09h45

Salut mouhiiddine.

Envoyé par mouhiiddine

mon problème c'est que les prix des taux se changent à compter de 16éme jours dans le cas ou la période dépasse 15 jours.

Vous explications étaient claires !

On applique deux tarifs selon la durée de la période.
Un premier tarif allant du 1er jour jusqu'au 15 ième jour compris.
Et si la période dépasse les 15 jours, on applique un autre tarif allant du 1er jour jusqu'au N ième jours.

Envoyé par mouhiiddine

je veux que ma requete applique deux prix pour meme periode cad dans la meme periode s'il depasse 15 jours a partir dr 16eme jour change le prix du taux exemple
Une periode de 1/1/2016 00:00 au 20/1/2016 23:00 je veux que ma requete calcule a la base de prix 80 dh pour les taux des 15 jours premiers apres 75 dh pour le reste de jour dans meme priode alors 3 taux par jour *80 pour le 15 jours premier +3taux par jour *75 pour le reste de la periode ce qui donne (3*15)*80+(3*5)*75 pour toute la periode.

Cela n'a plus rien avoir avec votre première demande.

Ce dont vous parlez ici, est un tarif dégressif, calculé par période.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`            integer  unsigned NOT NULL AUTO_INCREMENT primary key,
  `id_perso`      integer  unsigned NOT NULL,
  `date_depart`   datetime          NOT NULL,
  `date_retour`   datetime          NOT NULL,
  `ville`         varchar(255)      NOT NULL,
  `taux`          smallint unsigned     NULL DEFAULT NULL,
  `montant`       decimal(15,2)         NULL DEFAULT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
CREATE FUNCTION `taux` (retour datetime,
                        depart datetime)
RETURNS smallint unsigned
DETERMINISTIC
LANGUAGE SQL
BEGIN
  DECLARE _calc      smallint UNSIGNED DEFAULT 0;
  DECLARE _date      date              DEFAULT date(depart);
  DECLARE _time_deb  time;
  DECLARE _time_fin  time;
 
  WHILE (_date <= date(retour)) DO
    IF (_date = date(depart)) THEN SET _time_deb = time(depart); ELSE SET _time_deb = '00:00:00'; END IF;
    IF (_date = date(retour)) THEN SET _time_fin = time(retour); ELSE SET _time_fin = '24:00:00'; END IF;
 
    IF ((_time_deb <= '00:00:00') AND (_time_fin >= '05:00:00')) THEN SET _calc = _calc + 1; END IF;
    IF ((_time_deb <= '11:00:00') AND (_time_fin >= '14:00:00')) THEN SET _calc = _calc + 1; END IF;
    IF ((_time_deb <= '18:00:00') AND (_time_fin >= '21:00:00')) THEN SET _calc = _calc + 1; END IF;
 
    SET _date = date_add(_date, interval 1 day);
  END WHILE;
  RETURN _calc;
END
--------------
 
--------------
CREATE FUNCTION `tarif` (retour datetime,
                         depart datetime,
                         groupe varchar(255))
RETURNS smallint unsigned
DETERMINISTIC
LANGUAGE SQL
BEGIN
  DECLARE _duree  smallint UNSIGNED DEFAULT 0;
  DECLARE _dure1  smallint UNSIGNED DEFAULT 0;
  DECLARE _dure2  smallint UNSIGNED DEFAULT 0;
  DECLARE _prix1  decimal(15,2)     DEFAULT 0;
  DECLARE _prix2  decimal(15,2)     DEFAULT 0;
 
  SET _duree = datediff(retour, depart);
 
  IF (_duree > 15) THEN SET _dure1 = 15;     SET _dure2 = _duree - 15;
  ELSE                  SET _dure1 = _duree; SET _dure2 = 0;
  END IF;
 
  SET _prix1 = case groupe when 'groupe1' then 100 when 'groupe2' then  80 when 'groupe3' then  60 when 'groupe4' then  40 else  30 end;
  SET _prix2 = case groupe when 'groupe1' then  95 when 'groupe2' then  75 when 'groupe3' then  55 when 'groupe4' then  35 else  25 end;
 
  RETURN (_dure1 * _prix1 + _dure2 * _prix2);
END
--------------
 
--------------
INSERT INTO `test` (`id_perso`,`date_depart`,`date_retour`,`ville`) VALUES
  ( 4, '2016-05-02 04:04:00', '2016-05-04 20:00:00', 'casablanca'),
  ( 7, '2016-05-07 00:59:00', '2016-05-23 23:59:00', 'bou azer'),
  ( 9, '2016-05-12 00:00:00', '2016-06-12 00:00:00', 'biougra')
--------------
 
--------------
update `test` set taux    =         taux(date_retour, date_depart)
--------------
 
--------------
update `test` set montant = taux * tarif(date_retour, date_depart, 'groupe2')
--------------
 
--------------
select *,
       datediff(date_retour, date_depart)    as durée,
       cast(montant / taux as decimal(15,2)) as prix
from test
--------------
 
+----+----------+---------------------+---------------------+------------+------+-----------+-------+---------+
| id | id_perso | date_depart         | date_retour         | ville      | taux | montant   | durée | prix    |
+----+----------+---------------------+---------------------+------------+------+-----------+-------+---------+
|  1 |        4 | 2016-05-02 04:04:00 | 2016-05-04 20:00:00 | casablanca |    7 |   1120.00 |     2 |  160.00 |
|  2 |        7 | 2016-05-07 00:59:00 | 2016-05-23 23:59:00 | bou azer   |   50 |  63750.00 |    16 | 1275.00 |
|  3 |        9 | 2016-05-12 00:00:00 | 2016-06-12 00:00:00 | biougra    |   93 | 223200.00 |    31 | 2400.00 |
+----+----------+---------------------+---------------------+------------+------+-----------+-------+---------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Dans le deuxième exemple, nous trouvons comme prix = 1275.00
Celui-ci se décompose en :
--> 15 jours au tarif de 80 = 1200.00
--> 1 jour au tarif de 75 = 75.00
soit un total de 1275.00

Dans le troisième exemple, nous trouvons comme prix = 2400.00
--> 15 jours au tarif de 80 = 1200.00
--> 16 jours au tarif de 75 = 1200.00
soit un total de 2400.00

Le résultat final (ce que vous attendez) se trouve dans montant.

@+

**mouhiiddine** · 16/05/2016, 10h20

Salut
L'exemple est plus proche a ce que je cherche sauf que pour moi la durée ou le nombre de jours n'est pas inculu dans mais calcule puisque je compte le nombre des taux par jour pour moi le plus important c aplique par exemple pour le groupe 2 le prix d un tau 80 dh pour les 15 premier jour et 75 dh a partir de 16 eme jour
Alors comme jai explique (80 *par nombre taux des 15 premier jour +75 * par nombre taux de reste des jours si pour la meme periode si elle depasse 15 jours).

**Artemus24** · 16/05/2016, 14h58

Salut mouhiiddine.

Envoyé par mouhiiddine

... ce qui donne (3*15)*80+(3*5)*75 pour toute la periode.

Où le 3 correspond au taux, 15 et 5 à la durée du séjour soit au total 20 jours et 80 et 75 sont les tarifs à appliquer.

Envoyé par mouhiiddine

... sauf que pour moi la durée ou le nombre de jours n'est pas inculu ...

Si la durée ne rentre pas dans la calcul alors pourquoi l'avoir introduit dans l'exemple ci-dessus ?

Envoyé par mouhiiddine

Alors comme jai explique (80 *par nombre taux des 15 premier jour +75 * par nombre taux de reste des jours si pour la meme periode si elle depasse 15 jours).

C'est peut-être très clair dans votre tête, mais concernant les explications, vous devriez faire un effort pour être compréhensible.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`            integer  unsigned NOT NULL AUTO_INCREMENT primary key,
  `id_perso`      integer  unsigned NOT NULL,
  `date_depart`   datetime          NOT NULL,
  `date_retour`   datetime          NOT NULL,
  `ville`         varchar(255)      NOT NULL,
  `taux`          smallint unsigned     NULL DEFAULT NULL,
  `montant`       decimal(15,2)         NULL DEFAULT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
CREATE FUNCTION `taux` (retour datetime, depart datetime)
RETURNS smallint unsigned
DETERMINISTIC
LANGUAGE SQL
BEGIN
  DECLARE _calc      smallint UNSIGNED DEFAULT 0;
  DECLARE _date      date              DEFAULT date(depart);
  DECLARE _time_deb  time;
  DECLARE _time_fin  time;
 
  WHILE (_date <= date(retour)) DO
    IF (_date = date(depart)) THEN SET _time_deb = time(depart); ELSE SET _time_deb = '00:00:00'; END IF;
    IF (_date = date(retour)) THEN SET _time_fin = time(retour); ELSE SET _time_fin = '24:00:00'; END IF;
 
    IF ((_time_deb <= '00:00:00') AND (_time_fin >= '05:00:00')) THEN SET _calc = _calc + 1; END IF;
    IF ((_time_deb <= '11:00:00') AND (_time_fin >= '14:00:00')) THEN SET _calc = _calc + 1; END IF;
    IF ((_time_deb <= '18:00:00') AND (_time_fin >= '21:00:00')) THEN SET _calc = _calc + 1; END IF;
 
    SET _date = date_add(_date, interval 1 day);
  END WHILE;
  RETURN _calc;
END
--------------
 
--------------
CREATE FUNCTION `tarif` (retour datetime,
                         depart datetime,
                         groupe varchar(255))
RETURNS smallint unsigned
DETERMINISTIC
LANGUAGE SQL
BEGIN
  DECLARE _datdeb    date  default date(depart);
  DECLARE _quinze    date  default date_add(date(depart), interval 14 day);
 
  DECLARE _time_deb  time;
  DECLARE _time_fin  time;
 
  DECLARE _taux1     smallint UNSIGNED  default 0;
  DECLARE _taux2     smallint UNSIGNED  default 0;
 
  DECLARE _prix1     decimal(15,2);
  DECLARE _prix2     decimal(15,2);
 
  SET _quinze = case when _quinze > date(retour) then date(retour) else _quinze end;
 
  WHILE(_datdeb <= _quinze) DO
    IF (_datdeb  = date(depart)) THEN SET _time_deb = time(depart); ELSE SET _time_deb = '00:00:00'; END IF;
    IF (_datdeb  = date(retour)) THEN SET _time_fin = time(retour); ELSE SET _time_fin = '24:00:00'; END IF;
 
    IF ((_time_deb <= '00:00:00') AND (_time_fin >= '05:00:00')) THEN SET _taux1 = _taux1 + 1; END IF;
    IF ((_time_deb <= '11:00:00') AND (_time_fin >= '14:00:00')) THEN SET _taux1 = _taux1 + 1; END IF;
    IF ((_time_deb <= '18:00:00') AND (_time_fin >= '21:00:00')) THEN SET _taux1 = _taux1 + 1; END IF;
 
    SET _datdeb = date_add(_datdeb, interval 1 day);
  END WHILE;
 
  WHILE(_datdeb <= date(retour)) DO
    IF (_datdeb  = date(depart)) THEN SET _time_deb = time(depart); ELSE SET _time_deb = '00:00:00'; END IF;
    IF (_datdeb  = date(retour)) THEN SET _time_fin = time(retour); ELSE SET _time_fin = '24:00:00'; END IF;
 
    IF ((_time_deb <= '00:00:00') AND (_time_fin >= '05:00:00')) THEN SET _taux2 = _taux2 + 1; END IF;
    IF ((_time_deb <= '11:00:00') AND (_time_fin >= '14:00:00')) THEN SET _taux2 = _taux2 + 1; END IF;
    IF ((_time_deb <= '18:00:00') AND (_time_fin >= '21:00:00')) THEN SET _taux2 = _taux2 + 1; END IF;
 
    SET _datdeb = date_add(_datdeb, interval 1 day);
  END WHILE;
 
  SET _prix1 = case groupe when 'groupe1' then 100 when 'groupe2' then 80 when 'groupe3' then 60 when 'groupe4' then 40 else 30 end;
  SET _prix2 = case groupe when 'groupe1' then  95 when 'groupe2' then 75 when 'groupe3' then 55 when 'groupe4' then 35 else 25 end;
 
  RETURN (_prix1 * _taux1 + _prix2 * _taux2);
END
--------------
 
--------------
INSERT INTO `test` (`id_perso`,`date_depart`,`date_retour`,`ville`) VALUES
  ( 4, '2016-05-02 04:04:00', '2016-05-04 20:00:00', 'casablanca'),
  ( 7, '2016-05-07 00:59:00', '2016-05-23 23:59:59', 'bou azer'),
  ( 9, '2016-05-12 00:00:00', '2016-06-11 23:59:59', 'biougra')
--------------
 
--------------
update `test` set taux    =  taux(date_retour, date_depart)
--------------
 
--------------
update `test` set montant = tarif(date_retour, date_depart, 'groupe2')
--------------
 
--------------
select *, datediff(date_retour, date_depart) + 1 as durée
from test
--------------
 
+----+----------+---------------------+---------------------+------------+------+---------+-------+
| id | id_perso | date_depart         | date_retour         | ville      | taux | montant | durée |
+----+----------+---------------------+---------------------+------------+------+---------+-------+
|  1 |        4 | 2016-05-02 04:04:00 | 2016-05-04 20:00:00 | casablanca |    7 |  560.00 |     3 |
|  2 |        7 | 2016-05-07 00:59:00 | 2016-05-23 23:59:59 | bou azer   |   50 | 3970.00 |    17 |
|  3 |        9 | 2016-05-12 00:00:00 | 2016-06-11 23:59:59 | biougra    |   93 | 7200.00 |    31 |
+----+----------+---------------------+---------------------+------------+------+---------+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

id = 1 --> 7 * 80 + 0 * 75 = 560
id = 2 --> 44 * 80 + 6 * 75 = 3.970
id = 3 --> 45 * 80 + 48 * 75 = 7.200

Petit rappel : la fin de la journée est à "23:59:59".

En espérant, cette fois-ci, que c'est le bon calcul !

@+

**mouhiiddine** · 16/05/2016, 22h42

salut

merci beaucoup je viens de tester ta solution ça marche très bien après la création de la fonction tarif j'ai créé une requête de sélection comme suite

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT * ,taux(retour, depart) as taux,(case groupe when 'Groupe1' then cast( tarif(retour, depart,'Groupe1') as decimal(15,2)) when 'Groupe2' then cast(tarif(retour, depart,'Groupe2') as decimal(15,2)) when 'Groupe3' then cast(tarif(retour, depart,'Groupe3') as decimal(15,2)) when' Groupe4' then cast(tarif(retour, depart,'Groupe4') as decimal(15,2)) else cast(tarif(retour, depart,'Groupe5')  as decimal(15,2)) end ) as tarif
FROM a
WHERE ppr_ag=colname

et hop

CNT N° :
1227304
Nom :
TOUYER
Prenom :
MOUHI IDDINE

Grade :
TECHNICIEN 2éme GRADE
Groupe :
Groupe2

Date/Heure Départ Date/Heure Retour Objet Déplacement Lieu Nombre Taux Sommes Dues
2016-05-01 00:00:00 2016-05-01 22:03:00 formation Agadir 3 240.00
2016-05-02 00:00:00 2016-05-18 22:00:00 formation casablanca 51 3990.00

merci bien

**Artemus24** · 16/05/2016, 23h09

Salut mouhiiddine.

Comme je le suppose, tes montants sont avec des centimes.
Dans ce cas là, il faut revoir les types que tu vas utiliser.
Entre autre, la fonction tarif doit te retourner un type "decimal(15,2)".
Afin de ne pas avoir des conversions intempestives, il faut aussi mettre le taux et le prix dans ce type là.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
-- ================
-- Fonction `tarif`
-- ================
 
DELIMITER $$
 
CREATE FUNCTION `tarif` (retour datetime,
                         depart datetime,
                         groupe varchar(255))
RETURNS decimal(15, 2)
DETERMINISTIC
LANGUAGE SQL
BEGIN
  DECLARE _datdeb    date  default date(depart);
  DECLARE _quinze    date  default date_add(date(depart), interval 14 day);
 
  DECLARE _time_deb  time;
  DECLARE _time_fin  time;
 
  DECLARE _taux1     decimal(15,2)  default 0.0;
  DECLARE _taux2     decimal(15,2)  default 0.0;
  DECLARE _prix1     decimal(15,2)  default 0.0;
  DECLARE _prix2     decimal(15,2)  default 0.0;
 
  SET _quinze = case when _quinze > date(retour) then date(retour) else _quinze end;
 
  WHILE(_datdeb <= _quinze) DO
    IF (_datdeb  = date(depart)) THEN SET _time_deb = time(depart); ELSE SET _time_deb = '00:00:00'; END IF;
    IF (_datdeb  = date(retour)) THEN SET _time_fin = time(retour); ELSE SET _time_fin = '24:00:00'; END IF;
 
    IF ((_time_deb <= '00:00:00') AND (_time_fin >= '05:00:00')) THEN SET _taux1 = _taux1 + 1.0; END IF;
    IF ((_time_deb <= '11:00:00') AND (_time_fin >= '14:00:00')) THEN SET _taux1 = _taux1 + 1.0; END IF;
    IF ((_time_deb <= '18:00:00') AND (_time_fin >= '21:00:00')) THEN SET _taux1 = _taux1 + 1.0; END IF;
 
    SET _datdeb = date_add(_datdeb, interval 1 day);
  END WHILE;
 
  WHILE(_datdeb <= date(retour)) DO
    IF (_datdeb  = date(depart)) THEN SET _time_deb = time(depart); ELSE SET _time_deb = '00:00:00'; END IF;
    IF (_datdeb  = date(retour)) THEN SET _time_fin = time(retour); ELSE SET _time_fin = '24:00:00'; END IF;
 
    IF ((_time_deb <= '00:00:00') AND (_time_fin >= '05:00:00')) THEN SET _taux2 = _taux2 + 1.0; END IF;
    IF ((_time_deb <= '11:00:00') AND (_time_fin >= '14:00:00')) THEN SET _taux2 = _taux2 + 1.0; END IF;
    IF ((_time_deb <= '18:00:00') AND (_time_fin >= '21:00:00')) THEN SET _taux2 = _taux2 + 1.0; END IF;
 
    SET _datdeb = date_add(_datdeb, interval 1 day); 
  END WHILE;
 
  SET _prix1 = case groupe when 'groupe1' then 100.00 when 'groupe2' then 80.25 when 'groupe3' then 60.00 when 'groupe4' then 40.00 else 30.00 end;
  SET _prix2 = case groupe when 'groupe1' then  95.00 when 'groupe2' then 75.00 when 'groupe3' then 55.00 when 'groupe4' then 35.00 else 25.00 end;
 
  RETURN (_prix1 * _taux1 + _prix2 * _taux2);
END$$
DELIMITER ;

Pour tester la fonction, il est inutile de compliquer à outrance comme tu le fais.
Le select va se simpliquer comme l'exemple ci-après :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT *,
        taux(retour, depart)         as taux,
       tarif(retour, depart, groupe) as montant
FROM a
WHERE ppr_ag=colname

Comme "groupe" est une colonne, autant le mettre directement dans la fonction, au lieu de mettre la valeur, comme tu le fais dans ton exemple.

Tu peux passer à résolu ton sujet en cliquant sur le bouton en bas de la page.

@+

**mouhiiddine** · 16/05/2016, 23h34

merci bien ami ( professeur)

@+

Traitement des déplacements [MySQL-5.6]

Requêtes MySQL

Discussions similaires

Partager

Partager