Voir le flux RSS

Dendrite

SQL : Calculer le temps d'activité des personnes dans une structure

Note : 2 votes pour une moyenne de 5,00.
par , 12/06/2018 à 10h50 (158 Affichages)
Introduction

1) Que l’on soit une entreprise ou une association (structure), on a souvent besoin de stocker les heures faites par les personnes, et que cela puisse ensuite lancer tous les calculs de cumuls horaires que l’on veut (par personne, jour – par personne, semaine – par personne, mois – par personne, an ) et on multiplie ça par 2, car on veut la même chose par structure.

2) On aimerait ensuite que ces saisies soient sécurisées :
a) pas de fin >= début
b) pas de chevauchement horaire pour une même personne.

Bonne nouvelle, pour ces problématiques complexes, SQL sait faire !

1 a) Schéma de la base de données

Nom : schema.jpg
Affichages : 45
Taille : 47,3 Ko


2 petites tables au centre s’occupent de tout.
J’ai mis des double underscore entre horaire et saisie pour permettre (dans PHPMyAdmin seulement) de créer un folder horaire. Ca n’a aucun intérêt si votre base ne contient que ces 2 tables.
Les 4 vues à gauche sont les vues pour la structure, les 4 à droite sont les vues pour les personnes.

1 b) Création des 2 tables centrales

Code SQL : 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
 
CREATE TABLE IF NOT EXISTS `horaire__personne` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `civ` enum('M','Mme') NOT NULL,
  `nom_naissance` varchar(100) NOT NULL,
  `nom_usage` varchar(200) DEFAULT NULL,
  `prenom` varchar(100) NOT NULL,
  `naissance` date NOT NULL,
  `mail` varchar(200) DEFAULT NULL,
  `inscription` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `mail` (`mail`),
  KEY `nom_naissance` (`nom_naissance`) USING BTREE,
  KEY `nom_usage` (`nom_usage`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
INSERT INTO `horaire__personne` (`id`, `civ`, `nom_naissance`, `nom_usage`, `prenom`, `naissance`, `mail`, `inscription`) VALUES
(1, 'Mme', 'DUPONT', 'ZARMA', 'Corinne', '1965-12-31', 'cdupont@gmail.com', '2018-06-06 06:41:30'),
(2, 'M', 'ZARMA', NULL, 'Kamel', '1972-04-01', 'cdupont@gmail.com', '2018-06-06 06:48:45'),
(3, 'Mme', 'POPEYE', NULL, 'Louise', '2000-01-01', 'popeye@numericable.fr', '2018-06-06 06:50:12');
 
CREATE TABLE IF NOT EXISTS `horaire__saisie` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `personne_id` int(11) NOT NULL,
  `jour` date NOT NULL,
  `debut` time NOT NULL,
  `fin` time NOT NULL,
  PRIMARY KEY (`id`),
  KEY `personne_id` (`personne_id`),
  KEY `jour` (`jour`),
  KEY `debut` (`debut`),
  KEY `fin` (`fin`),
  KEY `periode` (`jour`,`debut`,`fin`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
 
INSERT INTO `horaire__saisie` (`id`, `personne_id`, `jour`, `debut`, `fin`) VALUES
(1, 1, '2018-06-01', '09:30:00', '14:00:00'),
(2, 1, '2018-06-01', '14:00:00', '18:00:00'),
(3, 1, '2018-06-04', '08:00:00', '11:50:00'),
(4, 1, '2018-06-04', '13:00:00', '18:15:00'),
(5, 2, '2018-06-01', '05:00:00', '13:00:00'),
(6, 2, '2018-06-04', '13:00:00', '21:00:00'),
(7, 3, '2018-06-01', '08:00:00', '23:59:59'),
(8, 3, '2018-05-31', '08:00:00', '20:00:00');

Table horaire__personne : Pourquoi s’embarrasser de nom_de_naissance et de nom_usage ? Parce que bien des femmes font le choix de changer de nom à l’occasion d’un mariage ou d’un divorce. Bien entendu, on a tous des noms de naissance, et on a… ou pas, un jour, des noms d’usage. Donc le SQL de la requête se réduira à
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
if(nom_usage is not null, nom_usage, nom_naissance) as nom

1 c) Comment faire la requête group by pour les cumuls ?

On est au coeur de la problématique.
Nous allons simplement détailler la requête de la vue v_jour_personne, le reste coule (relativement) de source.

Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
select distinct h.personne_id,
p.civ,
if(p.nom_usage is NULL, p.nom_naissance, p.nom_usage) as nom,
p.prenom,
timestampdiff(year, p.naissance,h.jour) as age, 
p.mail,
h.jour,
sec_to_time(sum(time_to_sec(timediff(h.fin,h.debut)))) as cumul
from horaire__saisie h
inner join horaire__personne p on h.personne_id=p.id
group by h.personne_id, h.jour
order by nom,p.prenom, h.jour desc

C’est bien sûr la ligne de cumul qui mérite quelques explications :
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
timediff(h.fin,h.debut)
Facile… puisque les champs sont de type time, on fait un timediff
puis on repasse tout en secondes avant de les cumuler (ici par personne, jour)
puis on repasse tout ce cumul en type time, car les humains risquent de trouver un peu complexe une durée exprimée en secondes…

Si l’on a compris cette requête, on peut se lancer dans la création de toutes les vues.


Ah non, il y avait encore un petit truc piégeux. Le group by semaine
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
select distinct h.personne_id,
p.civ,
if(p.nom_usage is NULL, p.nom_naissance, p.nom_usage) as nom,
p.prenom,
timestampdiff(year, p.naissance,h.jour) as age, 
p.mail,
concat(substr(YEARWEEK(jour,2),1,4),'-',substr(YEARWEEK(jour,2),5)) as semaine,
sec_to_time(sum(time_to_sec(timediff(h.fin,h.debut)))) as cumul
from horaire__saisie h
inner join horaire__personne p on h.personne_id=p.id
group by h.personne_id,YEARWEEK(jour,2)
order by nom,p.prenom,YEARWEEK(jour,2) desc;

Quand on repère les semaines de l’année, fonction YEARWEEK, on ne doit pas oublier qu’une semaine peut enjamber 2 mois, et plus filou encore, qu’elle peut enjamber 2 années… La semaine qui contient le 1er janvier est très particulière, toutes les fois où elle ne commence pas un Lundi en tout cas ! Et du coup, que va-t-on afficher à l’humain ? Surtout pas une concaténation YEAR + MONTH ou YEAR + WEEK, du coup, car ça créerait 2 lignes cette fameuse première semaine de l’année ! Donc on doit bien penser à manipuler la fonction YEARWEEK pour l’affichage, et rien d’autre, sinon le résultat va être pour le moins faussé.

Voici le code de construction des 8 vues

Code SQL : 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
 
-- --------------------------------------------------------
 
--
-- Structure de la vue `v_an`
--
 
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_an`  AS  select date_format(`h`.`jour`,'%Y') AS `an`,sec_to_time(sum(time_to_sec(timediff(`h`.`fin`,`h`.`debut`)))) AS `cumul` from `horaire__saisie` `h` group by `an` order by `an` desc ;
 
-- --------------------------------------------------------
 
--
-- Structure de la vue `v_an_personne`
--
 
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_an_personne`  AS  select `h`.`personne_id` AS `personne_id`,`p`.`civ` AS `civ`,if(isnull(`p`.`nom_usage`),`p`.`nom_naissance`,`p`.`nom_usage`) AS `nom`,`p`.`prenom` AS `prenom`,timestampdiff(YEAR,`p`.`naissance`,`h`.`jour`) AS `age`,`p`.`mail` AS `mail`,date_format(`h`.`jour`,'%Y') AS `an`,sec_to_time(sum(time_to_sec(timediff(`h`.`fin`,`h`.`debut`)))) AS `cumul` from (`horaire__saisie` `h` join `horaire__personne` `p` on((`h`.`personne_id` = `p`.`id`))) group by `h`.`personne_id`,`an` order by `nom`,`p`.`prenom`,`an` desc ;
 
-- --------------------------------------------------------
 
--
-- Structure de la vue `v_jour`
--
 
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_jour`  AS  select `h`.`jour` AS `jour`,sec_to_time(sum(time_to_sec(timediff(`h`.`fin`,`h`.`debut`)))) AS `cumul` from `horaire__saisie` `h` group by `h`.`jour` order by `h`.`jour` desc ;
 
-- --------------------------------------------------------
 
--
-- Structure de la vue `v_jour_personne`
--
 
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_jour_personne`  AS  select `h`.`personne_id` AS `personne_id`,`p`.`civ` AS `civ`,if(isnull(`p`.`nom_usage`),`p`.`nom_naissance`,`p`.`nom_usage`) AS `nom`,`p`.`prenom` AS `prenom`,timestampdiff(YEAR,`p`.`naissance`,`h`.`jour`) AS `age`,`p`.`mail` AS `mail`,`h`.`jour` AS `jour`,sec_to_time(sum(time_to_sec(timediff(`h`.`fin`,`h`.`debut`)))) AS `cumul` from (`horaire__saisie` `h` join `horaire__personne` `p` on((`h`.`personne_id` = `p`.`id`))) group by `h`.`personne_id`,`h`.`jour` order by `nom`,`p`.`prenom`,`h`.`jour` desc ;
 
-- --------------------------------------------------------
 
--
-- Structure de la vue `v_mois`
--
 
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_mois`  AS  select date_format(`h`.`jour`,'%Y-%m') AS `mois`,sec_to_time(sum(time_to_sec(timediff(`h`.`fin`,`h`.`debut`)))) AS `cumul` from `horaire__saisie` `h` group by `mois` order by `mois` desc ;
 
-- --------------------------------------------------------
 
--
-- Structure de la vue `v_mois_personne`
--
 
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_mois_personne`  AS  select `h`.`personne_id` AS `personne_id`,`p`.`civ` AS `civ`,if(isnull(`p`.`nom_usage`),`p`.`nom_naissance`,`p`.`nom_usage`) AS `nom`,`p`.`prenom` AS `prenom`,timestampdiff(YEAR,`p`.`naissance`,`h`.`jour`) AS `age`,`p`.`mail` AS `mail`,date_format(`h`.`jour`,'%Y-%m') AS `mois`,sec_to_time(sum(time_to_sec(timediff(`h`.`fin`,`h`.`debut`)))) AS `cumul` from (`horaire__saisie` `h` join `horaire__personne` `p` on((`h`.`personne_id` = `p`.`id`))) group by `h`.`personne_id`,`mois` order by `nom`,`p`.`prenom`,`mois` desc ;
 
-- --------------------------------------------------------
 
--
-- Structure de la vue `v_semaine`
--
 
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_semaine`  AS  select distinct concat(substr(yearweek(`h`.`jour`,2),1,4),'-',substr(yearweek(`h`.`jour`,2),5)) AS `semaine`,sec_to_time(sum(time_to_sec(timediff(`h`.`fin`,`h`.`debut`)))) AS `cumul` from `horaire__saisie` `h` group by yearweek(`h`.`jour`,2) order by yearweek(`h`.`jour`,2) desc ;
 
-- --------------------------------------------------------
 
--
-- Structure de la vue `v_semaine_personne`
--
 
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_semaine_personne`  AS  select distinct `h`.`personne_id` AS `personne_id`,`p`.`civ` AS `civ`,if(isnull(`p`.`nom_usage`),`p`.`nom_naissance`,`p`.`nom_usage`) AS `nom`,`p`.`prenom` AS `prenom`,timestampdiff(YEAR,`p`.`naissance`,`h`.`jour`) AS `age`,`p`.`mail` AS `mail`,concat(substr(yearweek(`h`.`jour`,2),1,4),'-',substr(yearweek(`h`.`jour`,2),5)) AS `semaine`,sec_to_time(sum(time_to_sec(timediff(`h`.`fin`,`h`.`debut`)))) AS `cumul` from (`horaire__saisie` `h` join `horaire__personne` `p` on((`h`.`personne_id` = `p`.`id`))) group by `h`.`personne_id`,yearweek(`h`.`jour`,2) order by `nom`,`p`.`prenom`,yearweek(`h`.`jour`,2) desc ;

2 a) Trigger d’insert


Code SQL : 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
USE horaire ;/*le nom de ma base de données*/
DROP TRIGGER IF EXISTS `insert_erreur`;
DELIMITER $$
CREATE TRIGGER `insert_erreur` BEFORE INSERT ON `horaire__saisie` FOR EACH ROW BEGIN
  DECLARE _msg  varchar(255);
 
  if (new.debut >= new.fin) then
        set _msg = concat('Insertion interdite ! horaire debut plus grand que horaire fin !');
     SIGNAL SQLSTATE VALUE '08888' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 8888;
  end if;
  if exists (select 1 from horaire__saisie where personne_id = new.personne_id and jour = new.jour and fin > new.debut and debut < new.fin) then
		set _msg = concat('Insertion interdite ! La période saisie recoupe une période déjà saisie !');
     SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 7777;
  end if;
END
$$
DELIMITER ;
2 b) Trigger d’update

Code SQL : 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
USE horaire;/*le nom de ma base de données*/
DROP TRIGGER IF EXISTS `update_erreur`;
DELIMITER $$
CREATE TRIGGER `update_erreur` BEFORE UPDATE ON `horaire__saisie` FOR EACH ROW BEGIN
  DECLARE _msg  varchar(255);
 
  if (new.debut >= new.fin) then
         set _msg = concat('Modification interdite ! horaire debut plus grand que horaire fin !');
     SIGNAL SQLSTATE VALUE '08888' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 8888;
  end if;
  if exists (select 1 from horaire__saisie where id != new.id and personne_id=new.personne_id and jour = new.jour and fin > new.debut and debut < new.fin) then
        set _msg = concat('Modification interdite ! La période saisie recoupe une période déjà saisie !');
     SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 7777;
  end if;
 
END
$$
DELIMITER ;

Voilà ! Enjoy !
Et si mes explications sont un peu trop expéditives, n'oubliez pas les commentaires. Je me ferai un plaisir de développer au besoin tel ou tel point.


Remerciements

Mes chaleureux remerciements à Artemus24 du forum developpez, pour ses compétences techniques, pour sa propension à les partager avec beaucoup de gentillesse ! Merci, merci, Artemus Agent secret au service du président Ulysses S. Grant !

Envoyer le billet « SQL : Calculer le temps d'activité des personnes dans une structure » dans le blog Viadeo Envoyer le billet « SQL : Calculer le temps d'activité des personnes dans une structure » dans le blog Twitter Envoyer le billet « SQL : Calculer le temps d'activité des personnes dans une structure » dans le blog Google Envoyer le billet « SQL : Calculer le temps d'activité des personnes dans une structure » dans le blog Facebook Envoyer le billet « SQL : Calculer le temps d'activité des personnes dans une structure » dans le blog Digg Envoyer le billet « SQL : Calculer le temps d'activité des personnes dans une structure » dans le blog Delicious Envoyer le billet « SQL : Calculer le temps d'activité des personnes dans une structure » dans le blog MySpace Envoyer le billet « SQL : Calculer le temps d'activité des personnes dans une structure » dans le blog Yahoo

Mis à jour 13/06/2018 à 09h07 par Dendrite

Catégories
PHP , Développement Web

Commentaires