Compter occurrences par table par date

**hichamus13** · 16/12/2020, 10h35

Bonjour à tous,
Je suis entrain de développer une application en PHP qui permet aux utilisateurs de rentrer leurs taches 2 fois par jour.
Des fois les utilisateurs de l'application, oublient de remplir leurs taches.
Je souhaiterais, pour m'assurer que tout le monde remplie toutes les taches 2 fois par jour, avant d'ouvrir l'application, créer une requête pour compter les occurrences des taches dans ma table par utilisateur et par date.
La date ne doit pas dépasser 7 jours à partir de la date du jour et ne prenant pas en compte les Week end.
Exemple, pour le 16/12 je compte les occurrences dans ma table de l'utilisateur X. Les dates que je prend en considération seront le 09,10,11,14 et 15/12 car je compte pas les Week end.
Je dois trouver 2 occurrences par date, si c'est inférieur j'invite l'utilisateur à remplir les dates manquantes.
J'ai essayé de faire ça mais je n'y arrive pas.
J'espère que j'ai été clair.
Merci d'avance de votre aide.

**escartefigue** · 16/12/2020, 11h00

Bonjour,

Pour ce besoin, une simple table de pointage AM/PM par employé ne suffira pas et compter le nombre de pointages sur une période ne suffit pas non plus.
Il faut également une table calendrier comprenant la notion de jour travaillé ou pas (WE, ferié...)
Il faut aussi une table des congés et absences: un employé n'ayant pas travaillé n'aura pas pointé, mais c'est tout à fait normal.

Donc sur la période examinée, il faut déduire le nombre de jours non travaillés ou en congés puis multiplier par deux (matin/soir) pour obtenir le nombre de pointages.

**hichamus13** · 16/12/2020, 11h26

Merci pour la réponse.
C'est une très bonne idée.
Je créerai donc, une table jour Fériés incluant les jours fériés et les Week end.
Pour la table Congés et absences, je pense pas besoin car l'utilisateur doit remplir qu'il était absent suite à la demande de mon patron.
Petite question, puis je avoir la structure de la requête qui permettra de déduire le nombre de jours non travaillés pour une période donnée et de calculer les occurrences ?
Merci

**Artemus24** · 17/12/2020, 14h03

Salut à tous.

Voici un exemple :

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
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `user`
--------------
 
--------------
CREATE TABLE `user`
(  `id`   integer unsigned  NOT NULL auto_increment Primary key,
   `nom`  varchar(255)      NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `user` (`nom`) VALUES
  ('Un'),('Deux'),('Trois')
--------------
 
--------------
select * from  `user`
--------------
 
+----+-------+
| id | nom   |
+----+-------+
|  1 | Un    |
|  2 | Deux  |
|  3 | Trois |
+----+-------+
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
(  `id`        integer unsigned  NOT NULL auto_increment Primary key,
   `date`      date              NOT NULL,
   `user_id`   integer unsigned  NOT NULL,
   `periode`   char(01)          NOT NULL,
   `tache_id`  integer unsigned      NULL,
   CONSTRAINT `FK_01` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `test` (`date`,`user_id`,`periode`,`tache_id`) VALUES
  ('2020-12-14', 1, 'M',   01),('2020-12-14', 1, 'S',   02),('2020-12-14', 2, 'M',   03),('2020-12-14', 2, 'S',   04),('2020-12-14', 3, 'M',   05),('2020-12-14', 3, 'S',   06),
  ('2020-12-15', 1, 'M',   07),('2020-12-15', 1, 'S',   08),('2020-12-15', 2, 'M',   09),('2020-12-15', 2, 'S',   10),('2020-12-15', 3, 'M',   11),('2020-12-15', 3, 'S',   12),
  ('2020-12-16', 1, 'M',   13),('2020-12-16', 1, 'S',   14),('2020-12-16', 2, 'M',   15),('2020-12-16', 2, 'S',   16),('2020-12-16', 3, 'M',   17),('2020-12-16', 3, 'S',   18),
  ('2020-12-17', 1, 'M',   19),('2020-12-17', 1, 'S', NULL),('2020-12-17', 2, 'M',   21),('2020-12-17', 2, 'S',   22),('2020-12-17', 3, 'M',   23),('2020-12-17', 3, 'S',   24),
  ('2020-12-18', 1, 'M',   25),('2020-12-18', 1, 'S',   26),('2020-12-18', 2, 'M',   27),('2020-12-18', 2, 'S',   28),('2020-12-18', 3, 'M',   29),('2020-12-18', 3, 'S',   30),
 
  ('2020-12-21', 1, 'M',   31),('2020-12-21', 1, 'S',   32),('2020-12-21', 2, 'M', NULL),('2020-12-21', 2, 'S', NULL),('2020-12-21', 3, 'M',   35),('2020-12-21', 3, 'S',   36),
  ('2020-12-22', 1, 'M',   37),('2020-12-22', 1, 'S',   38),('2020-12-22', 2, 'M',   39),('2020-12-22', 2, 'S',   40),('2020-12-22', 3, 'M',   41),('2020-12-22', 3, 'S',   42),
  ('2020-12-23', 1, 'M',   43),('2020-12-23', 1, 'S',   44),('2020-12-23', 2, 'M',   45),('2020-12-23', 2, 'S',   46),('2020-12-24', 3, 'M', NULL),('2020-12-23', 3, 'S',   48),
  ('2020-12-24', 1, 'M',   49),('2020-12-24', 1, 'S',   50),('2020-12-24', 2, 'M',   51),('2020-12-24', 2, 'S',   52),('2020-12-24', 3, 'M',   53),('2020-12-24', 3, 'S',   54),
  ('2020-12-25', 1, 'M',   55),('2020-12-25', 1, 'S',   56),('2020-12-25', 2, 'M',   57),('2020-12-25', 2, 'S',   58),('2020-12-25', 3, 'M',   59),('2020-12-25', 3, 'S',   60)
--------------
 
--------------
select * from  `test`
--------------
 
+----+------------+---------+---------+----------+
| id | date       | user_id | periode | tache_id |
+----+------------+---------+---------+----------+
|  1 | 2020-12-14 |       1 | M       |        1 |
|  2 | 2020-12-14 |       1 | S       |        2 |
|  3 | 2020-12-14 |       2 | M       |        3 |
|  4 | 2020-12-14 |       2 | S       |        4 |
|  5 | 2020-12-14 |       3 | M       |        5 |
|  6 | 2020-12-14 |       3 | S       |        6 |
|  7 | 2020-12-15 |       1 | M       |        7 |
|  8 | 2020-12-15 |       1 | S       |        8 |
|  9 | 2020-12-15 |       2 | M       |        9 |
| 10 | 2020-12-15 |       2 | S       |       10 |
| 11 | 2020-12-15 |       3 | M       |       11 |
| 12 | 2020-12-15 |       3 | S       |       12 |
| 13 | 2020-12-16 |       1 | M       |       13 |
| 14 | 2020-12-16 |       1 | S       |       14 |
| 15 | 2020-12-16 |       2 | M       |       15 |
| 16 | 2020-12-16 |       2 | S       |       16 |
| 17 | 2020-12-16 |       3 | M       |       17 |
| 18 | 2020-12-16 |       3 | S       |       18 |
| 19 | 2020-12-17 |       1 | M       |       19 |
| 20 | 2020-12-17 |       1 | S       |     NULL |
| 21 | 2020-12-17 |       2 | M       |       21 |
| 22 | 2020-12-17 |       2 | S       |       22 |
| 23 | 2020-12-17 |       3 | M       |       23 |
| 24 | 2020-12-17 |       3 | S       |       24 |
| 25 | 2020-12-18 |       1 | M       |       25 |
| 26 | 2020-12-18 |       1 | S       |       26 |
| 27 | 2020-12-18 |       2 | M       |       27 |
| 28 | 2020-12-18 |       2 | S       |       28 |
| 29 | 2020-12-18 |       3 | M       |       29 |
| 30 | 2020-12-18 |       3 | S       |       30 |
| 31 | 2020-12-21 |       1 | M       |       31 |
| 32 | 2020-12-21 |       1 | S       |       32 |
| 33 | 2020-12-21 |       2 | M       |     NULL |
| 34 | 2020-12-21 |       2 | S       |     NULL |
| 35 | 2020-12-21 |       3 | M       |       35 |
| 36 | 2020-12-21 |       3 | S       |       36 |
| 37 | 2020-12-22 |       1 | M       |       37 |
| 38 | 2020-12-22 |       1 | S       |       38 |
| 39 | 2020-12-22 |       2 | M       |       39 |
| 40 | 2020-12-22 |       2 | S       |       40 |
| 41 | 2020-12-22 |       3 | M       |       41 |
| 42 | 2020-12-22 |       3 | S       |       42 |
| 43 | 2020-12-23 |       1 | M       |       43 |
| 44 | 2020-12-23 |       1 | S       |       44 |
| 45 | 2020-12-23 |       2 | M       |       45 |
| 46 | 2020-12-23 |       2 | S       |       46 |
| 47 | 2020-12-24 |       3 | M       |     NULL |
| 48 | 2020-12-23 |       3 | S       |       48 |
| 49 | 2020-12-24 |       1 | M       |       49 |
| 50 | 2020-12-24 |       1 | S       |       50 |
| 51 | 2020-12-24 |       2 | M       |       51 |
| 52 | 2020-12-24 |       2 | S       |       52 |
| 53 | 2020-12-24 |       3 | M       |       53 |
| 54 | 2020-12-24 |       3 | S       |       54 |
| 55 | 2020-12-25 |       1 | M       |       55 |
| 56 | 2020-12-25 |       1 | S       |       56 |
| 57 | 2020-12-25 |       2 | M       |       57 |
| 58 | 2020-12-25 |       2 | S       |       58 |
| 59 | 2020-12-25 |       3 | M       |       59 |
| 60 | 2020-12-25 |       3 | S       |       60 |
+----+------------+---------+---------+----------+
--------------
select           t1.`nom`,
                 t2.`date`,
                 any_value(dayname(t2.`date`)) as jour,
                 any_value(`periode`)          as periode,
                 sum(case when t2.tache_id is null then 0 else 1 end) as nbre
 
           from  `user` as t1
 
left outer join `test` as t2
             on  t2.user_id = t1.id
 
          where  t2.`date` between current_date() and current_date + interval 6 day
       group by  t1.`nom`, t2.`date`
         having  nbre <> 2
       order by  t1.`nom`
--------------
 
+-------+------------+----------+---------+------+
| nom   | date       | jour     | periode | nbre |
+-------+------------+----------+---------+------+
| Deux  | 2020-12-21 | lundi    | M       |    0 |
| Trois | 2020-12-23 | mercredi | S       |    1 |
| Un    | 2020-12-17 | jeudi    | M       |    1 |
+-------+------------+----------+---------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Dans la table, vous rentrez que les jours travaillés.
Vous initialisez la colonne "tache_id" à NULL.
Quand l'utilisateur rempli sa tâche, la colonne "tache_id" va recevoir son identifiant qui va pointer sur la table des tâches.
Je ne sais pas trop ce que vous mettez dans cette table tâche, mais dans la table test, nous avons juste besoin de savoir si elle a été remplie ou pas.

Pour les jours glissants, le jour courant et le jour courant + 6 vous donne la semaine.
Il est inutile de rentrer les jours fériés et les Week-end, puisque ce sont des jours non travaillés.

Il manque en effet, la table des congés afin de savoir si un employé doit ou pas remplir ses tâches.

@+

**hichamus13** · 24/12/2020, 15h19

Merci pour votre réponse.
Puis-je créer une table regroupant les jours travaillés et tester par la suite si toutes les dates sont remplies par l'utilisateur.
La structure de ma table tache est la suivante : id, tache, date, matricule_user.
Ma table user est structurée comme suit : id, nom, prenom, matricule
Et ma table calendrier serait du genre , id, date

**manzeki** · 25/12/2020, 04h13

C'est une bonne bonne approche que celle de mettre en place une table calendrier contenant les dates des jours de travail.

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
--
DROP TABLE  IF EXISTS calendrier;
CREATE TABLE calendrier (
	date_id integer unsigned NOT NULL auto_increment Primary key,
	type_id smallint default 0,
	date date
);
 
--
DROP TABLE  IF EXISTS pointages;
CREATE TABLE pointages (
	pointage_id integer unsigned NOT NULL auto_increment Primary key,
	user_id integer REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
	status_id smallint default 0,
	pointage timestamp default now(),
	date date
);
 
--
DROP TABLE  IF EXISTS taches;
CREATE TABLE taches (
	tache_id integer unsigned NOT NULL auto_increment Primary key,
	user_id integer REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
	date timestamp default now(),
	tache varchar(256),
	details text
);
 
--
-- DROP TABLE  IF EXISTS users;
CREATE TABLE users (
	user_id integer unsigned NOT NULL auto_increment Primary key,
	matricule character(12),
	nom varchar(25),
	prenom varchar(25),
	mobile varchar(25),
	email varchar(25),
	creation timestamp default now()
);

**manzeki** · 26/12/2020, 05h45

Procédure permettant de créer le calendrier et les dates de pointage :

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
 
DELIMITER @@
CREATE PROCEDURE create_calendar( days smallint, debut date )
BEGIN
	INSERT INTO calendrier ( type_id, date )
	WITH RECURSIVE agenda (n, date) AS (
	  SELECT 1,
			IF (
				max(date),
				max(date) + interval 1 day,
				debut
			)
		FROM calendrier
	  UNION ALL
		SELECT n+1, date + interval 1 day
		FROM agenda
		WHERE n < days
	)
	SELECT 0, date
	FROM agenda
	WHERE date NOT IN (SELECT date FROM calendrier);
 
	--
	INSERT INTO pointages ( user_id, status_id, date )
	SELECT r.user_id, 1, a.date
	FROM users r, calendrier a
	WHERE (user_id, 1, date) date NOT IN (SELECT r.user_id, 1, date FROM calendrier)
	UNION
	SELECT r.user_id, 2, a.date
	FROM users r, calendrier a
	WHERE (user_id, 1, date) date NOT IN (SELECT r.user_id, 1, date FROM calendrier);
 
END @@
DELIMITER ;

Remplissage des tables calendrier et pointages pour une période de 365 jours commençant le '2020-12-01'

Code :

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

CALL create_calendar(365, '2020-12-01');

Le pointage consistera simplement à mettre à jour les colonnes status_id et pointage pour un employé donné :

Matin:

Code :

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

UPDATE pointages SET status_id = 3, pointage = now() WHERE status_id = 1 AND user_id = 1;

Soir :

Code :

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

UPDATE pointages SET status_id = 4, pointage = now() WHERE status_id = 2 AND user_id = 1;

Recherche des employés n'ayant pas encore pointé sur les 7 derniers jours

Code :

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

1
2
3
4
5
6
 
SELECT r.user_id, r.nom, a.date, a.pointage, a.status_id
FROM users r
INNER JOIN pointages a ON a.user_id = r.user_id
WHERE a.status_id IN (1, 2) AND a.date BETWEEN current_date - interval 7 day AND current_date - interval 1 day
ORDER BY a.date, r.user_id, a.status_id ASC;

**hichamus13** · 29/12/2020, 09h07

Je vais essayer tout ça.
Merci beaucoup

Compter occurrences par table par date

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager