Procédure stockée - extraction planning

**RamDevTeam** · 14/05/2014, 10h40

Bonjour à tous,

J'ai une table qui contient des infos sur des contrats de travail avec comme champs :
- Date de début de contrat
- date de fin de contrat
- nb heure lundi
- nb heure mardi
**
- nb heure dimanche

J'ai besoin de remplir une table contenant l'activité prévue sur une période en générant un enregistrement par jour de de présence prévue au contrat.

Exemple: si le contrat était borné entre le vendredi 2 et le vendredi 16 mai et que le salarié a 5 h le vendredi et 3h le lundi, je dois insérer les enregistrements suivant dans une table de pointage:

02/05/2014; 5
05/05/2014; 3
09/05/2014; 5
12/05/2014; 3
16/05/2014; 5

Pour l'instant cette partie était faite en code côté client mais pour diminuer les temps de traitement je voudrais pouvoir déclencher une procédure stockée qui reçoit une période en paramètre et qui traite de cette manière tous les contrats de la période.

Est-ce que c'est possible ?

Merci

**Ph. B.** · 15/05/2014, 00h57

Bonjour,

Envoyé par RamDevTeam

Pour l'instant cette partie était faite en code côté client mais pour diminuer les temps de traitement je voudrais pouvoir déclencher une procédure stockée qui reçoit une période en paramètre et qui traite de cette manière tous les contrats de la période.

Est-ce que c'est possible ?

Eh bien vu que cela existe sous forme de code procédural, il n'y a AMHA aucune impossibilité dans sa traduction sous forme de procédure(s) stockée(s)...
Personnellement, je créerais une première procédure stockée pour le traitement d'un contrat puis une deuxième procédure qui réalise la sélection des contrats et leur applique le traitement de la première...

**SergioMaster** · 15/05/2014, 08h42

Bonjour,

Moi c'est la structure de la table qui ne me dit rien !
un contrat est sur une seule semaine , plusieurs ? les ** ne sont pas très explicites contrairement a un DDL de création de la table

dire si cela est possible dépend beaucoup de cette fameuse structure !

**RamDevTeam** · 15/05/2014, 09h53

Envoyé par SergioMaster

Bonjour,

Moi c'est la structure de la table qui ne me dit rien !
un contrat est sur une seule semaine , plusieurs ? les ** ne sont pas très explicites contrairement a un DDL de création de la table

dire si cela est possible dépend beaucoup de cette fameuse structure !

Un contrat est sur plusieurs semaines, mais respecte un modèle de semaine type. (chaque semaine est considérée comme identique)
les champs lundi à Dimanche (d'où les **) contiennent le nombre d'heures prévus pour la journée.

La première étape est de générer toutes les dates d'interventions possibles pour un contrat de travail, avec la règle :

Pour chaque jour entre datedebutcontrat et datefincontrat
si (le jour est un lundi) et( le champ nb_heure_lundi >0)
alors insertion enregistrement
si (le jour est un mardi) et( le champ nb_heure_mardi >0)
alors insertion enregistrement
fin

extraits du code côté client :

Le qryAnalyse contient la liste des contrats de travail d'un salarié sur la période demandée.
ce code est exécuté pour chaque enregistrement de QryAnalyse.

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
// --- on parcours le calendrier de la période
                dt := BorneDebut;
                while dt<=BorneFin do
                begin
                  case integer(dt.DayOfWeek) of
                    0: if(QryAnalyse.fieldbyname('nbheures_dimanche').Ascurrency>0) then AjoutEnrg(dt, QryAnalyse.fieldbyname('nbheures_dimanche').Ascurrency);
                    1:  if(QryAnalyse.fieldbyname('nbheures_lundi').Ascurrency>0) then AjoutEnrg(dt,QryAnalyse.fieldbyname('nbheures_lundi').Ascurrency);
                    2:  if(QryAnalyse.fieldbyname('nbheures_mardi').Ascurrency>0) then AjoutEnrg(dt,QryAnalyse.fieldbyname('nbheures_mardi').Ascurrency);
                    3:  if(QryAnalyse.fieldbyname('nbheures_mercredi').Ascurrency>0) then AjoutEnrg(dt,QryAnalyse.fieldbyname('nbheures_mercredi').Ascurrency);
                    4:  if(QryAnalyse.fieldbyname('nbheures_jeudi').Ascurrency>0) then AjoutEnrg(dt,QryAnalyse.fieldbyname('nbheures_jeudi').Ascurrency);
                    5:  if(QryAnalyse.fieldbyname('nbheures_vendredi').Ascurrency>0) then AjoutEnrg(dt,QryAnalyse.fieldbyname('nbheures_vendredi').Ascurrency);
                    6:  if(QryAnalyse.fieldbyname('nbheures_samedi').Ascurrency>0) then AjoutEnrg(dt,QryAnalyse.fieldbyname('nbheures_samedi').Ascurrency);
                  end;
                  dt := dt.addDays(1);
                end;

Est-ce qu'il faut coder une boucle qui génère les dates (si oui comment ?), ou ne faut-il pas mieux se baser sur une table calendrier pour faire un select + jointure ?
comme on n'est pas sur un select simple, je ne suis pas sur que la solution de la table calendrier soit si performante que ça.

**SergioMaster** · 15/05/2014, 11h26

Bonjour,

Perso j'aurais créer ma structure de BDD différemment

Une table Contrat (code_contrat,date début,date fin etc...)
et une table 'jours contrat' avec codecontrat, jour (une date ou un numero en fonction date départ), nb heures (bref pratiquement la table à produire)

en supposant que :
1 - tout contrat 'commence un Lundi' même si pas d'heure
2 - Qu'il n'y ait au maximum que 7 jours

un Select de ce genre fonctionne

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT DATEDEBUT+0 AS DATEJOUR,NBHEURELUNDI AS HEURES FROM CONTRATS WHERE NBHEURELUNDI>0 AND ...... 
UNION
SELECT DATEDEBUT+1,NBHEUREMARDI FROM CONTRATS WHERE NBHEUREMARDI>0 AND ......
UNION 
SELECT DATEDEBUT+2,NBHEUREMERCREDI FROM CONTRATS WHERE NBHEUREMERCREDI>0 AND ......
UNION 
SELECT DATEDEBUT+3,NBHEUREJEUDI FROM CONTRATS WHERE NBHEUREJEUDI>0 AND ......
UNION 
SELECT DATEDEBUT+4,NBHEUREVENDREDI FROM CONTRATS WHERE NBHEUREVENDREDI>0 AND ......
UNION 
SELECT DATEDEBUT+5,NBHEURESAMEDI FROM CONTRATS WHERE NBHEURESAMEEDI>0 AND ......
UNION 
SELECT DATEDEBUT+6,NBHEUREDIMANCHE FROM CONTRATS WHERE NBHEUREDIMANCHE>0 AND ......

ou encore , avec CTE

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
WITH C AS 
(SELECT DATEDEBUT+0 AS DATEJOUR ,NBHEURELUNDI AS HEURES FROM CONTRATS WHERE NBHEURELUNDI>0  
UNION
SELECT DATEDEBUT+1,NBHEUREMARDI FROM CONTRATS WHERE NBHEUREMARDI>0 
UNION 
SELECT DATEDEBUT+2,NBHEUREMERCREDI FROM CONTRATS WHERE NBHEUREMERCREDI>0 
UNION 
SELECT DATEDEBUT+3,NBHEUREJEUDI FROM CONTRATS WHERE NBHEUREJEUDI>0 
UNION 
SELECT DATEDEBUT+4,NBHEUREVENDREDI FROM CONTRATS WHERE NBHEUREVENDREDI>0 
UNION 
SELECT DATEDEBUT+5,NBHEURESAMEDI FROM CONTRATS WHERE NBHEURESAMEEDI>0 
UNION 
SELECT DATEDEBUT+6,NBHEUREDIMANCHE FROM CONTRATS WHERE NBHEUREDIMANCHE>0
) 
 
SELECT * FROM C WHERE DATEJOUR BETWEEN .... AND ....;

il serait facile de faire un

Code :

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

1
2
 
 INSERT INTO TABLE (DATEJOUR,HEURE) SELECT * FROM C WHERE DATEJOUR BETWEEN .... AND ....;

à la place du SELECT de cette dernière solution

PS. j'aurais préféré la description de la table (DDL) au code Delphi

Edit . il serait peut être préférable d'utiliser la fonction DateAdd
pour ajouter les jours à la date (quoique , à ma souvenance cela devrait fonctionner)
il faudra peut être aussi CASTER le DATEDEBUT+0 AS DATEJOUR en DATE
ce que j'aurais pu tester , si j'avais eu le script de création de la table (d'où le PostScriptum)

**RamDevTeam** · 15/05/2014, 13h19

en supposant que :
1 - tout contrat 'commence un Lundi' même si pas d'heure
2 - Qu'il n'y ait au maximum que 7 jours

Ce n'est pas le cas.
Un contrat commence n'importe quel jour de la semaine, et peux durer aussi bien une semaine que 2 mois ou pas de date de fin car c'est un CDI.

Je n'avais pas donné le DDL des tables car elles contiennent pas mal d'autres champs qui embrouillerait.
Pour la table CONTRATDETRAVAIL
les champs DATEDEBUT, DATEFIN sont des TIMESTAMP (un date de début et de fin contient un jour et un horaire)
Les champs NBHEURES_xxx sont en DECIMAL(8,2)

La table CONTRATDETRAVAIL (version simplifiée)

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
CREATE TABLE CONTRATDETRAVAIL (
    REFCONTRAT                       INTEGER DEFAULT 0 NOT NULL,
    REFSALARIE                          INTEGER,
    DATEDEBUT                        TIMESTAMP DEFAULT '0001-01-01' NOT NULL,
    DATEFIN                          TIMESTAMP DEFAULT '0001-01-01' NOT NULL,
    NBHEUREHEBDO                     DECIMAL(18,2) DEFAULT 0 NOT NULL,
    NBHEURES_LUNDI                   DECIMAL(18,2) DEFAULT 0 NOT NULL,
    NBHEURES_MARDI                   DECIMAL(18,2) DEFAULT 0 NOT NULL,
    NBHEURES_MERCREDI                DECIMAL(18,2) DEFAULT 0 NOT NULL,
    NBHEURES_JEUDI                   DECIMAL(18,2) DEFAULT 0 NOT NULL,
    NBHEURES_VENDREDI                DECIMAL(18,2) DEFAULT 0 NOT NULL,
    NBHEURES_SAMEDI                  DECIMAL(18,2) DEFAULT 0 NOT NULL,
    NBHEURES_DIMANCHE                DECIMAL(18,2) DEFAULT 0 NOT NULL
 
);

La table POINTAGE (version simplifiée) à remplir :

Code :

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

1
2
3
4
5
6
CREATE TABLE POINTAGE (
    REFSALARIE      INTEGER,
    REFCONTRAT     INTEGER,
    DATEJOUR        TIMESTAMP,
    HTHEO           DECIMAL(15,2)
);

Je voudrais tenter de l'écrire en procédure stockée mais pour l'instant, je ne connais trop pas la syntaxe à utiliser.

L'enregistrement suivant dans la table contratdetravail

REFCONTRAT	REFSALARIE	DATEDEBUT	DATEFIN	NBHEURES_LUNDI	NBHEURES_MARDI	NBHEURES_MERCREDI	NBHEURES_JEUDI	NBHEURES_VENDREDI	NBHEURES_SAMEDI	NBHEURES_DIMANCHE
1	1	02/05/2014 07:00	16/05/2014 18:00	5	0	0	0	3	0	0

Doit donner en retour de fonction ou directement insérer dans la table pointage :

REFCONTRAT	REFSALARIE	DATEJOUR	HTHEO
1	1	02/05/2014	5
1	1	05/05/2014	3
1	1	09/05/2014	5
1	1	12/05/2014	3
1	1	16/05/2014	5

**SergioMaster** · 15/05/2014, 16h58

Ok , je n'avais pas vu que c'était des heures 'théoriques' et non des heures 'faites'

pour ce qui est de la date de début de contrat qui ne serait pas forcément un LUNDI cela peut se gérer

Code :

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

1
2
3
 
-- deuxième jour de la semaine courante
SELECT DATEADD(2+(EXTRACT(WEEKDAY FROM CURRENT_DATE)*-1) DAY TO CURRENT_DATE) FROM RDB$DATABASE

en première approche , voici comment il est possible de récupérer une semaine type (la première)

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
 
SET TERM ^ ;
 
CREATE PROCEDURE SEMAINE_TYPE
 ( CONTRATDU DATE,
   CONTRATAU DATE) 
RETURNS 
 ( REFSALARIE INTEGER,
   REFCONTRAT INTEGER,
   DATEJOUR DATE,
   HTHEO DECIMAL(18,2))
AS 
BEGIN
 
FOR 
 
WITH C AS 
(SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,1+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT) AS DATEJOUR ,NBHEURES_LUNDI AS HTHEO FROM CONTRATDETRAVAIL 
 WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_LUNDI>0  
UNION
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,2+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_MARDI FROM CONTRATDETRAVAIL
 WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_MARDI>0 
UNION 
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,3+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_MERCREDI FROM CONTRATDETRAVAIL
 WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_MERCREDI>0 
UNION 
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,4+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_JEUDI FROM CONTRATDETRAVAIL 
WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_JEUDI>0 
UNION 
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,5+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_VENDREDI FROM CONTRATDETRAVAIL 
WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_VENDREDI>0 
UNION 
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,6+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_SAMEDI FROM CONTRATDETRAVAIL 
WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_SAMEDI>0 
UNION 
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,7+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_DIMANCHE FROM CONTRATDETRAVAIL
WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_DIMANCHE>0
) 
 
-- c'est cette partie qu'il faut travailler  
SELECT * FROM C  INTO :REFSALARIE,:REFCONTRAT,:DATEJOUR,:HTHEO 
DO SUSPEND;  
 
END^
 
SET TERM ; ^

Edit : Obtenir un planning

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
 
SET TERM ^ ;
CREATE PROCEDURE PLANNING (
    CONTRATDU Date,
    CONTRATAU Date )
RETURNS (
    REFSALARIE Integer,
    REFCONTRAT Integer,
    DATEJOUR Date,
    HTHEO Decimal(18,2) )
AS
DECLARE VARIABLE D DATE;
DECLARE VARIABLE N INTEGER; 
BEGIN
 D = DATEADD(DAY,1+(EXTRACT(WEEKDAY FROM :CONTRATDU)*-1),:CONTRATDU);
 N = 0;
 WHILE (D<=:CONTRATAU) DO
 begin
   FOR SELECT REFSALARIE,REFCONTRAT,DATEJOUR+(:N*7),HTHEO 
   FROM SEMAINE_TYPE(:CONTRATDU,:CONTRATAU)
   WHERE DATEJOUR+(:N*7) BETWEEN :CONTRATDU AND :CONTRATAU 
   INTO :REFSALARIE,:REFCONTRAT,:DATEJOUR,:HTHEO
   DO SUSPEND; 
   N=N+1;
   D=D+(N*7); 
 END 
END^
SET TERM ; ^

Utilisation

Code :

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

1
2
SELECT p.REFSALARIE, p.REFCONTRAT, p.DATEJOUR, p.HTHEO
FROM PLANNING (CAST('01.05.2014' AS DATE),CAST('30.05.2014' AS DATE)) p

ou encore

Code :

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

1
2
 
INSERT INTO POINTAGE (REFSALARIE, REFCONTRAT, DATEJOUR, HTHEO) SELECT REFSALARIE, REFCONTRAT, DATEJOUR, HTHEO FROM  PLANNING (CAST('01.05.2014' AS DATE),CAST('30.05.2014' AS DATE))

**RamDevTeam** · 15/05/2014, 17h49

L'impilement de select me semble un peu compliqué. Je ne vois pas trop comment tu pourrais gérer une période de plusieurs semaine.

Sachant que de toutes manière, l'extraction du planning se fera uniquement à chaque changement de caractéristique du contrat de travail.
Est-ce qu'il n'y a pas moyen de faire une proc qui bouclerait sur les itérations de date entre datedebut et datefin pour faire les bons INSERT INTO

Exemple (truffé d'erreurs mais c'est pour l'idée)

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
SET TERM ^ ;
 
CREATE PROCEDURE EXtraire_Planning_theorique
 ( CONTRATDU DATE,
   CONTRATAU DATE) 
RETURNS 
 ( )
AS 
BEGIN
 
FOR 
 
WITH C AS 
(SELECT *  FROM CONTRATDETRAVAIL 
 WHERE DATEDEBUT<=:CONTRATDU AND DATEFIN>=:CONTRATAU 
) 
 
C.FIRST;
while not C.EOF do
  DtJour = :CONTRATDU;
  REPEAT
    NumJour = EXTRACT(WEEKDAY from :Dtjour) -1;
    case Numjour
     when 0 then
        IF (datedebut<=dtjour) and (datefin>=dtjour) then
             INSERT INTO POINTAGE(DATEJOUR, HTHEO) values(:dtjour,  NBHEURES_LUNDI);
     when 1 then
        IF (datedebut<=dtjour) and (datefin>=dtjour) then
             INSERT INTO POINTAGE(DATEJOUR, HTHEO) values(:dtjour,  NBHEURES_MARDI);
     .. idem pour les autres jours
    end 
   Dtjour = ADDDAYS(dtjour, 1);
  UNTIL DtJour>:CONTRATAU 
  C.NEXT
END
 
END^
 
SET TERM ; ^

**SergioMaster** · 15/05/2014, 18h04

Envoyé par RamDevTeam

L'empilement de select me semble un peu compliqué.

Sachant que de toutes manière, l'extraction du planning se fera uniquement à chaque changement de caractéristique du contrat de travail.
Est-ce qu'il n'y a pas moyen de faire une proc qui bouclerait sur les itérations de date entre datedebut et datefin pour faire les bons INSERT INTO

OK dans un Trigger alors AFTER UPDATE OR INSERT

il faudrait plutôt dans ce cas , utiliser un seul paramètre contrat (au lieu de la tranche de date)
et même principe

une procédure semainetype(CONTRAT)
la procedure Planning(contrat) qui récupérera grace au contrat les dates début et fin (parametres CONTRATDU CONTRAAU deviennent des VARIABLES
et dans le Trigger un INSERT OR UPDATE

on peut peut être tout faire dans le Trigger mais je n'en sais rien (je ne faisais que des tests)
attention il faudra peut être aussi gérer des deletes et autres cas (ajout d'heures à partir d'une date sur un même contrat)

Je ne vois pas trop comment tu pourrais gérer une période de plusieurs semaine.

je pense que c'est dans la suite postée lors de mes modifications donc peut être après ta lecture

Procédure stockée - extraction planning

SQL Firebird

Discussions similaires

Partager

Partager