Discussion :

[Ymox_]

Bonjour !

Je viens vers vous pour vous demander de l'aide pour une application de gestion de frais pour une entreprise. L'idée finale est d'afficher un récapitulatif des frais par mois sur une année glissante (avec total final), avec les totaux par départements, par personne et par type de frais. Les départements sont hiérarchisés, les types de frais aussi, et ces hiérarchies doivent être "respectées" dans les sommes (donc la somme d'un département contient les valeurs de ses départements descendants, idem pour les types de frais)
Les personnes doivent être affichées dans l'ordre alphabétique, et il y a aussi des chefs de département qui doivent du coup apparaître avant les autres personnes du département.

J'arrive à afficher mes sommes par département, par personne et par type de frais, dans l'ordre souhaité, mais maintenant je souhaiterais avoir le détail par type de frais pour les départements (toujours par mois sur une année glissante et avec le total sur cette année).
Là où je bute, c'est sur le fait que j'ai donc une partie de mon arborescence que je dois sommer, mais qui se trouve à différents niveaux. Les types de frais sont les mêmes pour tous les départements, mais tous n'ont pas déclaré des frais de même type de surcroît. Et le détail par type de frais pour le département doit donc prendre en compte les détails de ses descendants.

Comme j'ai de la peine à l'expliquer clairement (ce qui n'aide pas), je propose un exemple simplifié (et calculé à la main )

Direction					1025
			Communication		425
				Mobile	325
				Courrier	100
			Déplacements		600
				Avion	500
				Train	100
	Jean Dupont				150
			Communication		150
				Mobile	130
				Courrier	20
	Jeanne Durand				650
			Communication		150
				Mobile	100
				Courrier	50
			Déplacements		500
				Avion	500
	Sous-département 1				195
			Communication		95
				Mobile	85
				Courrier	10
			Déplacements		100
				Train	100
		John Smith			155
			Communication		55
				Mobile	50
				Courrier	5
			Déplacements		100
				Train	100
		Jane Doe			40
			Communication		40
				Mobile	35
				Courrier	5
	Sous-département 2				30
			Communication		30
				Mobile	10
				Courrier	20
		Catherine Tamalou			20
			Communication		20
				Courrier	20
		Justine Bobola			10
			Communication		10
				Mobile	10

Ce qu'il me manque actuellement, ce sont donc les lignes de types de frais entre celle qui contient le nom d'un département et la première personne qui suit.
L'idéal serait de pouvoir récupérer toutes les lignes de mon tableau en une fois, pour faire mes essais j'ai séparé en deux (la partie "qui fonctionne" et celle qui me pose problème)

Merci d'avance

[punkoff]

bonjour,


regardez du côté de GROUP BY / ROLLUP (voir CUBE au vu de la demande)

[Ymox_]

Bonjour,

J'utilise effectivement déjà ces deux instructions pour la partie qui fonctionne. Ce qui me pose problème actuellement, c'est que j'ai donc N niveaux de départements et M niveaux de types de frais. Il est clair qu'il ne me manque pas grand-chose, parce que mes derniers essais me montrent le détail des éléments qui sont directement dans le département, et mon total est identique dans mes deux résultats. C'est probablement parce que j'effectue un ROLLUP sur un ensemble trop restreint de données… ou alors que j'ai des regroupements inutiles à un certain point, voire les deux.

Il me vient à l'idée maintenant de faire des unions de groupes de M requêtes pour chaque niveau de département (soit quelque chose comme ça), mais ça ma paraît un peu lourd…

(voir CUBE au vu de la demande)

Je travaille avec MySQL, et sauf erreur de ma part, CUBE n'y existe pas (aurais-je mal placé mon sujet ?)

[aieeeuuuuu]

Bonjour,

Il faudrait :
1/ connaitre la structure de vos tables
2/ connaitre votre SGBD
3/ idéalement : avoir un jeu d'essai sous forme de CREATE TABLE + INSERT


La profondeur des hiérarchies est-elle toujours de deux ? (peut-il y avoir des sous-sous département et des sous-sous-catégories ?)

[Ymox_]

Comme mentionné dans mon précédent message, je travaille avec MySQL.

J'ai quatre tables de base :

• department
• person
• expense
• expense_type

Les hiérachies utilisent la représentation intervallaire, et j'ai une colonne qui me donne le niveau de l'élément dans la hiérarchie

Pour mes calculs avec ROLLUP, j'ai deux tables qui représentent de manière plate la hiérarchie des départements et des types de frais

• department_flat
• expense_type_flat

(Vous retrouverez tout ça dans le fichier SQL joint qui contient aussi un bref jeu de données d'exemple)

La profondeur des hiérarchies est actuellement limitée à trois pour les départements et à deux pour les types de frais. Seulement, l'affichage doit pouvoir changer : on peut choisir un département qui n'est pas racine, ce qui revient à afficher un sous-arbre avec le nœud choisi comme racine.
Notons aussi qu'à terme, il y aura plusieurs racines (notamment une qui servira pour des données inclassables, ces données provenant de fichiers CSV).

Alors qu'il y a des personnes dans les départements de tous niveaux, les types de frais sont actuellement répartis uniquement dans les feuilles.


Pour ma part, je serais cependant curieux de comprendre le comportement récurrent pour généraliser à N et M niveaux, et aussi dans le cas où les frais sont rattachés à n'importe quel type et pas seulement les types feuille

[SQLpro]

le seul moyen d'y parvenir est soit :
1) de faire une requête récursive
2) de remodéliser votre arbre sous forme intervallaire et de faire un simple SUM sur les différents intervalles souhaités.
À me lire : http://sqlpro.developpez.com/cours/s...te-recursives/

A +

[Ymox_]

le seul moyen d'y parvenir est soit :
1) de faire une requête récursive
2) de remodéliser votre arbre sous forme intervallaire et de faire un simple SUM sur les différents intervalles souhaités.

Merci SQLpro, je vais regarder les requêtes récursives, ça me fait un bel os à ronger.

J'ai quelques question cependant sur votre seconde proposition, je ne suis pas sûr d'avoir bien compris.
Pour ce qui est de re-modéliser mon arbre sous forme intervallaire, vous entendez fusionner les deux que j'utilise en un seul ? Les deux arbres sont actuellement déjà fournis ainsi, mais séparément, et c'est peut-être ce qui me bloque, à savoir comment avoir la somme des sous-arbres de niveau N+1 et plus pour le second arbre, alors que je suis au niveau N.

[aieeeuuuuu]

Merci SQLpro, je vais regarder les requêtes récursives, ça me fait un bel os à ronger.

Ce n'est pas implémenté sous MySQL !

[Ymox_]

Est-ce que je devrais adapter le titre pour y mettre le langage que j'utilise, ou faudrait-il avoir placé le sujet dans le forum MySQL ? N'y aurait-il pas moyen de le déplacer ?

Ce n'est pas implémenté sous MySQL !

Ça reste un os à ronger, maintenant je n'ai pas dit que ça me convenait

Pour ce qui est de re-modéliser mon arbre sous forme intervallaire, vous entendez fusionner les deux que j'utilise en un seul ?

Je m'excuse, mais je pensais pouvoir recevoir une réponse, merci.

[aieeeuuuuu]

Que donne ceci :

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
 
SELECT
  departement, 
  first_name, 
  last_name,
  expense_type,
  montant
FROM (
   SELECT 
      d.lft,
      d.name AS departement,
      p.first_name  ,
      p.last_name,
      et.name as expense_type,
      SUM(e.amount) AS Montant
  FROM department d
  INNER JOIN department sd
      ON	sd.lft BETWEEN d.lft AND d.rgt
  INNER JOIN person p
      ON		p.department_id = sd.id
  INNER JOIN expense e
      ON	e.person_id = p.id
  INNER JOIN expense_type et
      ON		et.id = e.expense_type_id
  INNER JOIN expense_type ets
      ON	ets.lft BETWEEN et.lft AND et.rgt
  GROUP BY 
      d.name,
      et.name, 
      p.first_name,
      p.last_name
      WITH ROLLUP
   ) T
WHERE departement  IS NOT NULL
AND (last_name IS NOT NULL OR first_name IS NULL)
ORDER BY lft,2,3,4

[Ymox_]

Wow, merci ! On y est presque… apparemment, il me manque les totaux par types de frais de premier niveau, je n'ai que les sommes pour les niveaux feuille actuels. Serait-ce parce qu'il n'y a des frais que dans ces niveaux ? J'aimerais avoir les "regroupements" par niveau parent aussi…

Je vais analyser ce que vous m'avez fourni plus en détail demain, peut-être même que j'arriverai à comprendre ce qu'il me manque.
À cela j'ajouterais encore un ordre qui tient compte des managers, et je serai bon !

(Et apparemment, même pas besoin de mes tables de hiérarchie plate, ça c'est un point très intéressant, je trouve )

[aieeeuuuuu]

hmmm, ce n'est pas à vous qu'il va manquer quelque chose... c'est à MySQL.

Il vous faudrait une option WITH CUBE pour le GROUP BY, ou mieux, la possibilité de spécifier les regroupements voulus, dans votre cas ceci :

Code :

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

1
2
3
4
5
6
7
8
9
 
	GROUP BY GROUPING SETS(
 
		(d.name,p.first_name,p.last_name),
		(d.name,et.name,p.first_name,p.last_name),
		(d.name, et.name),
		(d.name)
 
		)

Malheureusement, il me semble que MySQL n'implémente ni l'un, ni l'autre.

A moins qu'il existe une "fonction magique" comme le fait parfois MySQL pour contourner ce genre de carence, je pense que vous n'avez d'autre solution que de faire un UNION de la même requête avec un ordre différent pour le GROUP BY afin d'obtenir les groupements qu'il vous manque...

Quelque chose comme ceci peut être :

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
 
 
SELECT 
	T.departement,
	T.first_name,
	T.last_name,
	T.expense_type,
	T.Montant
FROM (
	SELECT 
		MIN(d.lft)lft,
		d.name departement ,
		p.first_name first_name ,
		p.last_name last_name,
		et.name expense_type,
		SUM(e.amount) AS Montant
	FROM department d
	INNER JOIN department sd
		ON	sd.lft BETWEEN d.lft AND d.rgt
	INNER JOIN person p
		ON		p.department_id = sd.id
	INNER JOIN expense e
		ON	e.person_id = p.id
	INNER JOIN expense_type et
		ON		et.id = e.expense_type_id
	INNER JOIN expense_type ets
		ON	ets.lft BETWEEN et.lft AND et.rgt
 
	GROUP BY 
		et.name	
		,d.name
		,p.first_name
		,p.last_name
 
		WITH ROLLUP
 
	UNION 
 
	SELECT 
		MIN(d.lft),
		d.name,
		p.first_name  ,
		p.last_name,
		et.name,
		SUM(e.amount) AS Montant
	FROM department d
	INNER JOIN department sd
		ON	sd.lft BETWEEN d.lft AND d.rgt
	INNER JOIN person p
		ON		p.department_id = sd.id
	INNER JOIN expense e
		ON	e.person_id = p.id
	INNER JOIN expense_type et
		ON		et.id = e.expense_type_id
	INNER JOIN expense_type ets
		ON	ets.lft BETWEEN et.lft AND et.rgt
 
 
	GROUP BY 
		d.name
		,p.first_name
		,p.last_name
		,et.name
		WITH ROLLUP
)T
 
WHERE departement IS NOT NULL
AND(last_name IS NOT NULL OR first_name IS NULL)
ORDER BY T.lft, T.first_name, T.expense_type

[Ymox_]

Cette dernière requête ne fait pas apparaître les niveaux parent des types de frais, malheureusement.

Une question au passage : pourquoi grouper sur les noms et prénoms plutôt que sur l'ID de la personne ? Afin d'éviter une opération de tri supplémentaire ?

[aieeeuuuuu]

Pire, elle est fausse, et compte deux fois certains montants.
Vu les limitations sur la spécification des regroupements avec MySQL, je ne suis pas sûr qu'on puisse y arriver de cette façon...

Du coup...j'ai un peu honte de cette solution, mais en l'absence de requêtes récursives, de clause CUBE, et fonctions analytiques, bref, de tout ce qui pourrait être utile pour résoudre proprement ce problème, vous pouvez essayer ceci :

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
 
	SELECT 
 
		d.name departement ,
		p.first_name  ,
		p.last_name,
		et.name expense_type,
		SUM(e.amount) AS Montant
	FROM department d
	INNER JOIN department sd
		ON	sd.lft BETWEEN d.lft AND d.rgt
	INNER JOIN 
			(
				SELECT	id, first_name, last_name, department_id, manager
				FROM	person 
				UNION ALL
				SELECT  id, NULL, NULL, department_id, NULL
				FROM	person
			)p
		ON		p.department_id = sd.id
		AND		(p.first_name IS NULL OR p.department_id = d.id)
	INNER JOIN expense e
		ON	e.person_id = p.id
 
	INNER JOIN expense_type ets
		ON 	ets.id = e.expense_type_id
	INNER JOIN (
						SELECT id, lft, rgt, name
						FROM expense_type 
						UNION ALL 
						SELECT id, lft, rgt, NULL
						FROM expense_type 
 
			)	et
		ON	ets.lft BETWEEN et.lft AND et.rgt
		AND		(et.name IS NOT NULL OR et.lft = ets.lft)
 
	GROUP BY 
		et.name	
		,d.name
		,p.first_name
		,p.last_name
 
ORDER BY 
	MIN(d.lft), 
	CASE WHEN p.last_name IS NULL THEN 0 ELSE 1 END, 
	MAX(p.manager) DESC,
	p.last_name, 
	p.first_name, 
	CASE WHEN et.name IS NULL THEN 0 ELSE 1 END, 
	MIN(et.lft)

L'idée étant de "dupliquer" chaque personne avec un homologue sans nom (NULL) ni prenom (NULL), mais avec les mêmes caractéristiques (id, lft, rgt,...). Ces "fausse personnes" seront au final toutes regroupées avec le GROUP BY, permettant ainsi de calculer les Totaux.
On procède de façon analogue avec les "expense_type".

[Ymox_]

Oui, voilà, merci beaucoup !

J'en étais à tenter de faire ça avec une union entre mes groupements qui tenaient compte des personnes (donc la partie que j'avais réussi à calculer, assez aisément d'ailleurs, normal, pas de montants à prendre 2 fois (une fois pour la personne et une fois pour le détail par type pour le département — ce qui explique cette solution de "dédoubler les personnes en tant qu'anonymes")) et ceux qui n'en tenaient pas compte, mais je n'arrivais pas à les réordonner comme je le souhaitais… qui plus est, je manipulais mes tables de "hiérarchies plates" pas très pratiques…

Je vais tenter de me débrouiller depuis là pour récupérer mes poids de gauche et droite pour les départements et les types de frais (ainsi que les niveaux), je vais les utiliser pour la représentation graphique et au pire, je reviens par ici

Encore merci aieeeuuuuu !

[Ymox_]

Tiens, il n'est plus possible de modifier ses messages après avoir mis que le sujet était résolu ? Ou c'est le fait de l'avoir "réouvert" qui fait ça ? Ou je suis complètement aveugle ? Ou c'est plus simplement le temps qui a passé ?

Bref.
Après avoir regardé plus en détails, je me rends compte qu'il doit encore y avoir une subtilité.
J'ai une version "une requête après l'autre" ou je construis mon tableau en ayant pour base les données regroupées par département, personne et type de frais, et quand je change de département, je vais chercher les totaux pour ce département et ses enfants par types de frais.
Je sors quelques 4500 lignes avec cette méthode — et ma requête initiale ne me montre pas les personnes qui n'ont pas de notes de frais.
La requête ci-dessus ne m'en sort que 4000 pour le même jeu de données…

[aieeeuuuuu]

Quelles sont les données qui ne ressortent pas ?

[Ymox_]

Désolé, au temps pour moi, j'avais des doublons dans mon système actuel (qui plus est, il y a un type de frais qui n'y est systématiquement pas présent, donc je me retrouve avec un nombre de lignes plus important avec votre solution qu'avec la mienne, et ce à juste titre, mais vu que je compte abandonner cette manière de faire…)
Il n'y aurait plus qu'à calculer la ligne de total global en cas de racines multiples (que j'aimerais afficher en tête). Est-ce que c'est possible de l'avoir en même temps que le reste des résultats ?

[fsmrel]

Bonjour Ymox,


J’ai découvert tardivement votre problème, pour lequel vous avez vraisemblablement trouvé une solution satisfaisante puisqu’il est marqué résolu.

Néanmoins, vu l’intérêt du sujet, j’ai fait un essai pour produire une solution, basée non pas sur l’approche intervallaire (dont je ne suis pas familier), mais sur la récursivité.

Pour répondre à votre dernière question, l’objet est ici de cumuler à tous les niveaux (département et type de dépense) les dépenses effectuées par les personnes (table EXPENSE). Je n’ai pas pris en compte les fourchettes des dates des dépenses, mais je pense qu’il ne s’agit pas de l’aspect le plus compliqué des choses...

Incidemment, j’ai dû découper mon message, jugé trop long pour l’éditeur utilisé par DVP...


1ère partie, modélisation des données

Puisque je n’utilise pas l’approche intervallaire, j’ai transformé la structure de la base de données que vous aviez proposée.

Diagramme avec MySQL Workbench :




Scripts de création des tables :

Table des départements

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE DEPARTMENT
(
        DeptId            VARCHAR(08)     NOT NULL
      , DeptNom           VARCHAR(64)     NOT NULL 
    , CONSTRAINT DEPARTMENT_PK PRIMARY KEY (DeptId)
) ;

Table de la hiérarchie des départements

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE DEPARTMENT_HIERARCHIE
(
        DeptId            VARCHAR(08)     NOT NULL
      , DeptParent        VARCHAR(08)     NOT NULL 
    , CONSTRAINT DEPARTMENT_HIERARCHIE_PK PRIMARY KEY (DeptId)
    , CONSTRAINT DEPARTMENT_HIERARCHIE_ENFANT_FK FOREIGN KEY (DeptId) REFERENCES DEPARTMENT (DeptId) 
    , CONSTRAINT DEPARTMENT_HIERARCHIE_PARENT_FK FOREIGN KEY (DeptParent) REFERENCES DEPARTMENT (DeptId) 
) ;

Table des personnes

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE PERSON
(
       PsnId             INT             NOT NULL
     , PsnPrenom         VARCHAR(64)     NOT NULL 
     , PsnNom            VARCHAR(64)     NOT NULL 
     , DeptId            VARCHAR(08)     NOT NULL 
  , CONSTRAINT PERSON_PK PRIMARY KEY (PsnId)
  , CONSTRAINT PERSON_DEPARTMENT_FK FOREIGN KEY (DeptId) REFERENCES DEPARTMENT (DeptId) 
) ;

Table des personnes qui sont chefs de département

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE CHEF_DEPARTMENT  
(
       PsnId            INT              NOT NULL
     , DeptId           VARCHAR(08)      NOT NULL 
  , CONSTRAINT CHEF_DEPARTMENT_PK PRIMARY KEY (DeptId)
  , CONSTRAINT CHEF_DEPARTMENT_AK UNIQUE (PsnId)
  , CONSTRAINT CHEF_DEPARTMENT_PSN_FK FOREIGN KEY (PsnId) REFERENCES PERSON (PsnId)
  , CONSTRAINT CHEF_DEPARTMENT_DEPT_FK FOREIGN KEY (DeptId) REFERENCES DEPARTMENT (DeptId) 
) ;

Table des types de dépense

Code SQL :

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

1
2
3
4
5
6
7
CREATE TABLE EXPENSE_TYPE  
(
       ExpTypeId        INT              NOT NULL
     , ExpTypeNom       VARCHAR(64)      NOT NULL 
     , ExpTypeDescr     VARCHAR(64)      NOT NULL 
  , CONSTRAINT EXPENSE_TYPE_PK PRIMARY KEY (ExpTypeId)
) ;

Table de la hiérarchie des types de dépense

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE EXPENSE_TYPE_HIERARCHIE
(  
       ExpTypeId          INT              NOT NULL
     , ExpTypeParent      INT              NOT NULL
  , CONSTRAINT EXPENSE_TYPE_HIERARCHIE_PK PRIMARY KEY (ExpTypeId)
  , CONSTRAINT EXPENSE_TYPE_HIERARCHIE_ENFANT_FK FOREIGN KEY (ExpTypeId) REFERENCES EXPENSE_TYPE (ExpTypeId)
  , CONSTRAINT EXPENSE_TYPE_HIERARCHIE_PARENT_FK FOREIGN KEY (ExpTypeParent) REFERENCES EXPENSE_TYPE (ExpTypeId) 
) ;

Table des dépenses

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE EXPENSE
(  
       ExpenseId          INT              NOT NULL
     , ExpTypeId          INT              NOT NULL
     , PsnId              INT              NOT NULL
     , DateExp            DATE             NOT NULL
     , Amount            DECIMAL(6,2)      NOT NULL                
  , CONSTRAINT EXPENSE_PK PRIMARY KEY (ExpenseId)
  , CONSTRAINT EXPENSE_TYPE_EXPENSE_FK FOREIGN KEY (ExpTypeId) REFERENCES EXPENSE_TYPE (ExpTypeId)
  , CONSTRAINT EXPENSE_TYPE_PERSON_FK FOREIGN KEY (PsnId) REFERENCES PERSON (PsnId) 
) ;

Un jeu d’essai

Les départements (D0 désigne l’entreprise elle-même, considérée comme le super-département) :

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
 
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D0', 'Dubicobit') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D2', 'Direction des achats') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D21', 'B2B') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D21A', 'B2B or not 2B') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D22', 'Marketing') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D221', 'Prix/Produit') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D23', 'Commerce') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D24', 'Presse') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D2210', 'Cogitation') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D2211', 'Suivi') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D2211B', 'Conseil') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D2211C', 'Bienveillance') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D2211D', 'Repos') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D2212', 'Remue-méninges') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D2212A', 'Sous-Remue-méninges') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D2212AA', 'Cuisine') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D2212AAA', 'Arrière-cuisine') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D3', 'Filiale Naudin') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D31', 'Ets Volfoni') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D311', 'La péniche') ;
INSERT INTO DEPARTMENT (DeptId, DeptNom) VALUES ('D3111', 'Le clapier') ;

La hiérarchie des départements :

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
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D2', 'D0') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D21', 'D2') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D21A', 'D21') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D22', 'D2') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D221', 'D22') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D23', 'D2') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D24', 'D2') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D2210', 'D221') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D2211', 'D2210') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D2211B', 'D2211') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D2211C', 'D2211') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D2211D', 'D2211') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D2212', 'D2210') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D2212A', 'D2212') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D2212AA', 'D2212A') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D2212AAA', 'D2212AA') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D3', 'D0') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D31', 'D3') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D311', 'D31') ;
INSERT INTO DEPARTMENT_HIERARCHIE (DeptId, DeptParent) VALUES ('D3111', 'D311') ;

Les personnes :

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
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (1, 'Lucien', 'Rocher', 'D2') ; -- chef du D2
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (2, 'Anthony', 'Paulin', 'D2') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (3, 'Coline', 'Tisserand', 'D24') ; -- chef du D24
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (4, 'Angelina', 'Rocher', 'D24') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (5, 'Pierre', 'Robert', 'D24') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (6, 'Ludovic', 'Gilbert', 'D22') ; -- chef du D22
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (7, 'Jérémy', 'Sauvage', 'D22') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (8, 'Anthony', 'Godin', 'D22') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (9, 'Julie', 'Garnero', 'D22') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (10, 'Romain', 'Thomas', 'D22') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (11, 'Florian', 'Mahieu', 'D22') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (12, 'Dominique', 'Aubin', 'D22') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (13,'Jean-Marie', 'Villette', 'D23') ;  -- chef du D23
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (14, 'Arnaud', 'Rodrigues', 'D23') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (15, 'Nocilas', 'Augier', 'D23') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (16, 'Florian', 'Jacquier', 'D23') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (17, 'Lucien', 'Rocher', 'D23') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (18, 'Joseph', 'Cardon', 'D23') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (19, 'Jean-Michel', 'Frey', 'D23') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (20, 'Franck', 'Teissier', 'D2') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (21, 'Amélie', 'Tison', 'D2') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (22, 'Fabrice', 'Deplanque', 'D2') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (27, 'Sylvain', 'Davy', 'D2') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (28, 'Jean-Michel', 'Caille', 'D2') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (29, 'Philippe', 'Allais', 'D2') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (30, 'Lionel', 'Potier', 'D2') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (31, 'Henri', 'Allart', 'D2') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (2211, 'Maurice', 'Champo', 'D221') ; --  chef du D221
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (2212, 'Luc', 'Poilau', 'D221') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (2213, 'Hubert', 'de La Pâte Feuilletée', 'D2211') ; -- ne dépense rien
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (3000, 'Fernand', 'Naudin', 'D3') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (3101, 'Raoul', 'Volfoni', 'D31') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (3102, 'Paul', 'Volfoni', 'D31') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (3111, 'Antoine', 'Delafoy', 'D311') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (31111, 'Patricia', 'Monpetit', 'D31') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (31120, 'Francis', 'Folace', 'D2211B') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (31121, 'René', 'Descartes', 'D2211') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (31122, 'Hubert', 'De Guerrelasse', 'D2211D') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (31123, 'Amélie', 'Méleau', 'D2211D') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (31124, 'Lulu', 'La nantaise', 'D2212') ;
INSERT INTO PERSON (PsnId, PsnPrenom, PsnNom, DeptId) VALUES (31125, 'Teddy', 'de Montréal', 'D2212AAA') ;

Les chefs de département

Code SQL :

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

1
2
3
4
5
6
INSERT INTO CHEF_DEPARTMENT (PsnId, DeptId) VALUES (1, 'D2') ;
INSERT INTO CHEF_DEPARTMENT (PsnId, DeptId) VALUES (6, 'D22') ;
INSERT INTO CHEF_DEPARTMENT (PsnId, DeptId) VALUES (2211, 'D221') ;
INSERT INTO CHEF_DEPARTMENT (PsnId, DeptId) VALUES (13, 'D23') ;
INSERT INTO CHEF_DEPARTMENT (PsnId, DeptId) VALUES (3, 'D24') ; 
INSERT INTO CHEF_DEPARTMENT (PsnId, DeptId) VALUES (3000, 'D3') ;

Les types de dépense

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
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (0, 'Dépense neutre', 'Univers des types de dépense') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (1, 'Autres', 'Erreurs') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (2, 'Multimédia', 'Mobile, TV…') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (3, 'MOBILE', 'Communication mobile') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (4, 'TV', 'Swisscom TV') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (5, 'AUTRES', 'Autres remboursements') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (6, 'AUTO', 'Véhicule') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (7, 'CARBURANT', 'Carburant') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (8, 'NETTOYAGE', 'Nettoyage') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (9, 'Déplacements', 'Déplacements') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (10, 'Electro', 'Electronique') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (11, 'Loisirs', 'Et on se distrait !') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (12, 'Courses', 'Faut aller vite') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (17, 'Mazout', 'Chauffage') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (20, 'Cinéma', 'Pour se détendre') ;
INSERT INTO EXPENSE_TYPE (ExpTypeId, ExpTypeNom, ExpTypeDescr) VALUES (30, 'Bagatelle', 'On n’est pas de bois') ;

La hiérarchie des types de dépense

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
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (1, 0) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (2, 10) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (3, 2) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (4, 3) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (5, 3) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (6, 9) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (7, 0) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (8, 9) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (9, 12) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (10, 11) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (11, 0) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (12, 0) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (20, 2) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (30, 11) ;
INSERT INTO EXPENSE_TYPE_HIERARCHIE (ExpTypeId, ExpTypeParent) VALUES (17, 7) ;

Les dépenses

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
INSERT INTO EXPENSE (ExpenseId, ExpTypeId, PsnId, DateExp, Amount) VALUES 
(1, 3, 1, '2014-01-01', 31.00),   -- Lucien
(134, 4, 1, '2014-01-01', 9.50),
(500, 5, 1, '2014-01-01', 0.6),
 
(2, 3, 2, '2014-01-01', 1.00),  -- Anthony
(135, 4, 2, '2014-01-01', 9.50),
 
(3, 3, 3, '2014-01-01', 28.00),  --  Coline
(136, 4, 3, '2014-01-01', 9.70),
 
(137, 4, 4, '2014-01-01', 39.54),  -- Angelina
(4, 3, 4, '2014-01-01', 45.00),
 
(5, 3, 5, '2014-01-01', 85.00),
(12, 3, 6, '2014-01-01', 225.00),
(145, 4, 6, '2014-01-01', 19.63),
(175, 7, 13, '2014-01-01', 79.12),
(176, 7, 18, '2014-01-01', 88.75),
(177, 7, 19, '2014-01-01', 238.42),
 
(31, 3, 31, '2014-01-01', 25.00),
(189, 7, 31, '2014-01-01', 52.74),
 
(190, 4, 2211, '2014-01-07', 40),
(191, 4, 2211, '2014-01-08', 60),
(192, 4, 2211, '2014-01-07', 75),
(195, 6, 2211, '2014-01-07', 123),
(196, 6, 2211, '2014-01-07', 877),
(197, 7, 2211, '2014-01-07', 100),
 
(193, 4, 2212, '2014-01-08', 60),
(194, 5, 2212, '2014-01-08', 40),
(198, 7, 2212, '2014-01-08', 99),
(199, 20, 2212, '2014-01-08', 200),
(200, 8, 2212, '2014-01-08', 274),
(201, 30,2212, '2014-01-08', 300),
(202, 10,2212, '2014-02-01', 47),
(203, 2,2212, '2014-02-01', 4.33),
 
(301,3,3000,'2014-02-01',50),
(302,4,3101,'2014-02-01',50),
(303,5,3102,'2014-02-01',50),
(304,6,3111,'2014-02-01',50), 
(305,7,31111,'2014-02-01',50),
(306,30,31120,'2014-02-01',500),
(307,30,31120,'2014-02-08',400),
(308,30,31120,'2014-02-08',99.99),
(309,3, 31120, '2014-02-02', 22.02) ,
(310, 9, 31122,'2014-02-02', 3.06),
(311, 7, 31122,'2014-02-02', 12.00),
(312, 4, 31122,'2014-02-02', 78.10),
(313, 12, 31121,'2014-02-02', 10.00) 
 , (314, 17, 31123,'2014-02-02', 314.00)
  , (315, 4, 31124,'2014-02-02', 10.00)
 , (316, 7, 31125,'2014-02-02', 6.00)
 , (317, 12, 31125,'2014-02-02', 3.00)
 , (318, 10, 31125,'2014-02-02', 5.00)
;

La suite dans le prochain message.

[fsmrel]

2e partie


Vu le problème posé, pour effectuer les cumuls des montants, j’ai donc préféré utiliser des procédures récursives (en tenant compte du contexte MySQL).


Dans un premier temps, on restreint la hiérarchie des départements à prendre en en compte, en fonction du département D choisi comme racine. On insère dans une table (nommée DEP_DESC) le sous-arbre extrait de la table DEPARTMENT_HIERARCHIE et de racine D.

Procédure correspondante : YmoxRecursionNiveauHierarchieDepartements.

Pour créer et tester la procédure :

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
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
-- *************************************************************************************************************
-- L'objet de cette procédure est de constituer la pile DEP_DESC des descendants d'un département donné :
-- Pouvoir traiter de D2 sans parasitage par son cousin D3.
-- *************************************************************************************************************
USE Ymox ;
 
DROP TABLE IF EXISTS MA_TRACE_DESC ;
DROP TABLE IF EXISTS DEP_DESC ;
 
COMMIT ;
 
-- ----------------------------------------------------------------
-- Au cas où l'on aurait à tracer ce qui se passe....
-- ----------------------------------------------------------------
CREATE TABLE MA_TRACE_DESC
(
    Trace       VARCHAR(256)     NOT NULL
) ;
 
-- ----------------------------------------------------------------
-- Hiérarchie des départements descendants d'un département racine
-- ----------------------------------------------------------------
CREATE TABLE  DEP_DESC
(
    DeptRacine       VARCHAR(8)            NOT NULL,
    DeptParent       VARCHAR(8)            NOT NULL,
    DeptId           VARCHAR(8)            NOT NULL,
    DeptNiveau       INT                   NOT NULL
  , PRIMARY KEY (DeptId)  
) ;
 
COMMIT ;
 
-- ----------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS YmoxRecursionNiveauHierarchieDepartements ;
 
DELIMITER GO
 
-- -----------------------------------------------------------------------------------------------
CREATE PROCEDURE YmoxRecursionNiveauHierarchieDepartements
(
   IN LaRacine VARCHAR(8), 
   INOUT Statut VARCHAR(128)
)
 
BEGIN
 
    DECLARE theNiveau INT DEFAULT 0;
    DECLARE Kount INT ;
    DECLARE StatutOK VARCHAR(20) DEFAULT 'OK' ;
    DECLARE PseudoAdam VARCHAR(8) DEFAULT '!!' ;
 
    INSERT INTO MA_TRACE_DESC values (CONCAT('LaRacine = ', LaRacine)) ;
 
    SET theNiveau = (SELECT DISTINCT COALESCE (MAX(DeptNiveau) + 1, 1) FROM DEP_DESC) ;
 
    INSERT INTO MA_TRACE_DESC values (CONCAT('theNiveau = ', theNiveau)) ;
 
    IF theNiveau = 1 THEN
 
        SET Statut = StatutOK ; 
 
        INSERT INTO MA_TRACE_DESC values (CONCAT('On amorce la pompe, theNiveau = ', theNiveau)) ;
 
-- ------------------------------------------
        SET Kount = (SELECT COUNT(*) FROM  DEPARTMENT WHERE  DeptId = LaRacine) ;
 
        INSERT INTO MA_TRACE_DESC values (CONCAT('Kount LaRacine FROM  DEPARTMENT = ', Kount)) ;
 
        IF Kount = 0 THEN     -- (Kount)
 
            INSERT INTO MA_TRACE_DESC VALUES (CONCAT(' YmoxRecursionNiveauHierarchieDepartements, Département inconnu  : ', LaRacine)) ;
 
            SET Statut = (SELECT  CONCAT('YmoxRecursionNiveauHierarchieDepartements, département inconnu : ', LaRacine))  ;
 
        ELSE    
 
            SET Kount = (SELECT COUNT(*) FROM  DEPARTMENT_HIERARCHIE WHERE  DeptId = LaRacine) ;
 
            INSERT INTO MA_TRACE_DESC values (CONCAT('Kount LaRacine FROM  DEPARTMENT_HIERARCHIE = ', Kount)) ;
 
            IF Kount > 0 THEN
                -- On n'est pas au niveau de l'entreprise, mais d'une direction ou d'un département
                INSERT INTO DEP_DESC (DeptRacine, DeptParent, DeptId, DeptNiveau)
                    SELECT LaRacine, DeptParent, DeptId, theNiveau
                    FROM   DEPARTMENT_HIERARCHIE 
                    WHERE  DeptId = LaRacine ;
 
            ELSE
                -- on est au niveau racine (cas où la racine est l’entreprise elle-même)
 
                INSERT INTO MA_TRACE_DESC values (CONCAT('On traite de la racine : ', LaRacine)) ;
 
                INSERT INTO DEP_DESC (DeptRacine, DeptParent, DeptId, DeptNiveau)
                    SELECT distinct LaRacine, PseudoAdam, LaRacine, theNiveau
                    FROM   DEPARTMENT_HIERARCHIE 
                    WHERE  DeptParent = LaRacine ;
 
            END IF ;
 
         -- On entame la récursion pour chercher un premier niveau de descendants 
            CALL YmoxRecursionNiveauHierarchieDepartements(LaRacine, Statut) ;
        END IF ;       -- (Kount)
 
    ELSE     -- (niveau)
 
        INSERT INTO MA_TRACE_DESC values ('On est dans la partie récursive') ;
 
        IF Statut = StatutOK THEN   
 
        --  On compte le nombre de descendants qui restent à empiler 
            SET Kount = (SELECT COUNT(*) 
                         FROM   (SELECT x.DeptId
                                FROM   DEP_DESC AS x  INNER JOIN DEPARTMENT_HIERARCHIE AS y ON x.DeptId = y.DeptParent 
                                WHERE  DeptNiveau = TheNiveau- 1) AS z) ;
 
            INSERT INTO MA_TRACE_DESC values (CONCAT('Kount = ', Kount)) ;
 
            IF Kount > 0 THEN            
 
            --  On empile des descendants 
                INSERT INTO DEP_DESC (DeptRacine, DeptParent, DeptId, DeptNiveau) 
                    SELECT x.DeptRacine, y.DeptParent, y.DeptId, TheNiveau  
                    FROM   DEP_DESC AS x  INNER JOIN DEPARTMENT_HIERARCHIE AS y ON x.DeptId = y.DeptParent 
                    WHERE  x.DeptNiveau = TheNiveau - 1 ; 
 
             -- Et on repart pour un tour de manège
                CALL YmoxRecursionNiveauHierarchieDepartements(LaRacine, Statut) ;
 
            END IF ;   
 
        END IF ;   
 
    END IF; -- (niveau) 
 
END GO
 
DELIMITER ;
 
 SET @@GLOBAL.max_sp_recursion_depth = 15;
 SET @@session.max_sp_recursion_depth = 15; 
 
SET @Departement := 'D2' ;  --  Département considéré comme racine
 --   SET @Departement := 'D22' ;  
 --   SET  @Departement := '!!' ; 
 --   SET  @Departement := 'D0' ; 
 
CALL YmoxRecursionNiveauHierarchieDepartements(@Departement, @Statut);
 
-- Au résultat
 
SELECT 'Statut : ', @Statut ;
 
SELECT * FROM DEP_DESC ORDER BY DeptNiveau, DeptId ;

Exemple avec le département D2 considéré comme racine :

Table DEP_DESC

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
DeptRacine    DeptParent    DeptId    DeptNiveau
D2            D0            D2        1
D2            D2            D21       2
D2            D2            D22       2
D2            D2            D23       2
D2            D2            D24       2
D2            D22           D221      3
D2            D221          D2210     4
D2            D2210         D2211     5
D2            D2210         D2212     5
D2            D2211         D2211B    6
D2            D2211         D2211C    6
D2            D2211         D2211D    6

Dans un 2e temps, on détermine le niveau hiérarchique de chaque type de dépense .La table (nommée EXP_DEC) qui résulte de cette opération n’est pas indispensable, mais la présence du niveau hiérarchique peut être utile pour présenter les résultats des calculs des dépenses selon l’ordre hiérarchique des types de dépenses. A signaler que le type de dépense 0 est considéré comme racine dans l’arbre des types de dépense (table DEPARTMENT_HIERARCHIE).

Procédure correspondante : YmoxRecursionNiveauHierarchieTypesdeDepenses.

Pour créer et tester la procédure :

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
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
-- *********************************************************************************************************************************
-- L'objet de cette procédure est de constituer la pile EXP_DEC du niveau où se situe chaque descendant d'un type de dépense donné,
-- sans être parasité par les branches cousines.
-- *********************************************************************************************************************************
USE Ymox ;
 
DROP TABLE IF EXISTS MA_TRACE_EXP_DESC ;
DROP TABLE IF EXISTS EXP_DEC ;
 
COMMIT ;
 
-- ----------------------------------------------------------------
-- Au cas où l'on aurait à tracer ce qui se passe....
-- ----------------------------------------------------------------
CREATE TABLE MA_TRACE_EXP_DESC
(
    Trace       VARCHAR(256)     NOT NULL
) ;
 
-- ----------------------------------------------------------------
-- Hiérarchie des types de dépense descendant d'un type racine
-- ----------------------------------------------------------------
CREATE TABLE  EXP_DEC
(
    ExpTypeRacine       VARCHAR(8)            NOT NULL,
    ExpTypeParent       VARCHAR(8)            NOT NULL,
    ExpTypeId           VARCHAR(8)            NOT NULL,
    ExpTypeNiveau       INT                   NOT NULL
  , PRIMARY KEY (ExpTypeId) 
) ;
 
COMMIT ;
 
-- ----------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS YmoxRecursionNiveauHierarchieTypesdeDepenses ;
 
DELIMITER GO
 
-- -----------------------------------------------------------------------------------------------
CREATE PROCEDURE YmoxRecursionNiveauHierarchieTypesdeDepenses
(
   IN TypeDepenseRacine VARCHAR(8), 
   INOUT Statut VARCHAR(128)
)
 
BEGIN
 
    DECLARE theNiveau INT DEFAULT 0;
    DECLARE Kount INT ;
    DECLARE StatutOK VARCHAR(20) DEFAULT 'OK' ;
    DECLARE PseudoAdam INT DEFAULT -1 ;
 
    INSERT INTO MA_TRACE_EXP_DESC values ('======================== Tour de manège ================================================') ;
    INSERT INTO MA_TRACE_EXP_DESC values (CONCAT('TypeDepenseRacine = ', TypeDepenseRacine)) ;
 
    SET theNiveau = (SELECT DISTINCT COALESCE (MAX(ExpTypeNiveau) + 1, 1) FROM EXP_DEC) ;
 
    INSERT INTO MA_TRACE_EXP_DESC values (CONCAT('theNiveau = ', theNiveau)) ;
 
    IF theNiveau = 1 THEN   -- (theNiveau)
 
        SET Statut = StatutOK ; 
 
        INSERT INTO MA_TRACE_EXP_DESC values (CONCAT('On amorce la pompe, theNiveau = ', theNiveau)) ;
 
        IF TypeDepenseRacine = 0 THEN
 
            INSERT INTO EXP_DEC (ExpTypeRacine, ExpTypeParent, ExpTypeId, ExpTypeNiveau)
                    VALUES (TypeDepenseRacine, TypeDepenseRacine - 1, TypeDepenseRacine, 1) ;
 
        ELSE
 
            INSERT INTO EXP_DEC (ExpTypeRacine, ExpTypeParent, ExpTypeId, ExpTypeNiveau)
                SELECT TypeDepenseRacine, ExpTypeParent, ExpTypeId, theNiveau
                FROM   EXPENSE_TYPE_HIERARCHIE 
                WHERE  ExpTypeId = TypeDepenseRacine ;
 
        END IF ;
-- ------------------------------------------
        SET Kount = (SELECT COUNT(*) FROM  EXPENSE_TYPE WHERE  ExpTypeId = TypeDepenseRacine) ;
 
        INSERT INTO MA_TRACE_EXP_DESC values (CONCAT('Kount FROM  EXPENSE_TYPE = ', Kount)) ;
 
        IF Kount = 0 THEN  --      (Kount)
 
            INSERT INTO MA_TRACE_EXP_DESC VALUES (CONCAT('Département inconnu  : ', TypeDepenseRacine)) ;
 
            SET Statut = (SELECT  CONCAT('YmoxRecursionNiveauHierarchieTypesdeDepenses, département inconnu : ', TypeDepenseRacine))  ;
 
        ELSE     -- (Kount)
 
         --  On entame la récursion pour chercher un premier niveau de descendants 
             CALL YmoxRecursionNiveauHierarchieTypesdeDepenses(TypeDepenseRacine, Statut) ;
        END IF ; -- (Kount)
 
    ELSE  -- -- (theNiveau)
 
        INSERT INTO MA_TRACE_EXP_DESC values ('On est dans la partie récursive') ;
 
        IF Statut = StatutOK THEN  --  (StatutOK)
 
        --  On compte le nombre de descendants qui restent à empiler 
            SET Kount = (SELECT COUNT(*) 
                         FROM   (SELECT x.ExpTypeId
                                FROM   EXP_DEC AS x  INNER JOIN EXPENSE_TYPE_HIERARCHIE AS y ON x.ExpTypeId = y.ExpTypeParent 
                                WHERE  ExpTypeNiveau = TheNiveau- 1) AS truc) ;
 
            INSERT INTO MA_TRACE_EXP_DESC values (CONCAT('Kount = ', Kount)) ;
 
            IF Kount > 0 THEN          --   (Kount)        
 
                   --  On empile des descendants 
                       INSERT INTO EXP_DEC (ExpTypeRacine, ExpTypeParent, ExpTypeId, ExpTypeNiveau) 
                           SELECT x.ExpTypeRacine, y.ExpTypeParent, y.ExpTypeId, TheNiveau  
                           FROM   EXP_DEC AS x  INNER JOIN EXPENSE_TYPE_HIERARCHIE AS y ON x.ExpTypeId = y.ExpTypeParent 
                           WHERE  x.ExpTypeNiveau = TheNiveau - 1 ; 
 
                     -- Et on repart pour un tour de manège
                     CALL YmoxRecursionNiveauHierarchieTypesdeDepenses(TypeDepenseRacine, Statut) ;
 
            END IF ;  -- (Kount)
 
        END IF ;   -- (StatutOK)
 
    END IF;  -- (theNiveau)
 
END GO
 
DELIMITER ;
 
 SET @@GLOBAL.max_sp_recursion_depth = 15;
 SET @@session.max_sp_recursion_depth = 15; 
 
-- SET  @TypeDepense := 9999 ;
-- SET @TypeDepense := 12 ;  
-- SET @TypeDepense := 11 ;  
SET @TypeDepense := 0 ; 
 
CALL YmoxRecursionNiveauHierarchieTypesdeDepenses(@TypeDepense, @Statut);
 
-- Au résultat
 
SELECT 'YmoxRecursionNiveauHierarchieTypesdeDepenses, Statut : ', @Statut AS Statut ;
 
SELECT *, '' AS 'EXP_DEC' FROM EXP_DEC ORDER BY ExpTypeNiveau, ExpTypeId ;

Exemple avec le type de dépense 0 (racine) :

Table EXP_DEC

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
ExpTypeRacine    ExpTypeParent    ExpTypeId    ExpTypeNiveau
            0               -1            0                1
            0                0            1                2
            0               11           10                3
            0                0           11                2
            0                0           12                2
            0                7           17                3
            0               10            2                4
            0                2           20                5
            0                2            3                5
            0               11           30                3
            0                3            4                6
            0                3            5                6
            0                9            6                4
            0                0            7                2
            0                9            8                4
            0               12            9                3