SQL : Calculer le temps d'activit� des personnes dans une structure
par
, 12/06/2018 � 10h50 (2772 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
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', '[email protected]', '2018-06-06 06:41:30'), (2, 'M', 'ZARMA', NULL, 'Kamel', '1972-04-01', '[email protected]', '2018-06-06 06:48:45'), (3, 'Mme', 'POPEYE', NULL, 'Louise', '2000-01-01', '[email protected]', '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 :
Facile� puisque les champs sont de type time, on fait un timediff
Code SQL : S�lectionner tout - Visualiser dans une fen�tre � part timediff(h.fin,h.debut)
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
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 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 ;
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 !