Système de configuration de devis multi niveaux

**Piou2fois** · 12/02/2019, 21h12

Bonjour
J'aimerai pour une application interne (Access + SQL Server) faire une système de devis à multi niveau
J'entends par là ce genre de schéma :
Item 1
+ Item 2 - 200€
+ Item 3
++ Item 4 - 300€
++ Item 5 - 500€
+Item 6
++ Item 7 - 600€
++ Item 8
+++ Item 9 - 600€
+++ Item 10 - 100€

L'idée que j'ai c'est d'avoir une seule table avec comme colonnes:
DETAILS_ID comme identifiant unique
DETAILS_PARENT_ID identifier le parent
DETAILS_DESCRIPTION comme description de l'élément
DETAILS_PRICE qui serait le prix, sachant que pour les éléments avec enfant il serait inutile

Maintenant je me demande si c'est une méthode jouable et surtout comment gérer la récursivité (procédure stockée ?). Ensuite je pense que dans Access je verrais pour utiliser un contrôle type treeview ou si vous avez des idées (un peu hors sujet là) autre chose, mais à part créer des sous formulaires à la volée je vois pas comment...

D'avance merci pour vos conseils

**sebastien_m** · 13/02/2019, 08h27

salut !

vas donc jeter un coup d'oeil là dessus :

https://sqlpro.developpez.com/cours/...te-recursives/

**Piou2fois** · 13/02/2019, 09h23

Merci
Je vais voir pour me faire un jeu de données et trouver comment faire ce que je souhaite (avoir à chaque étage le prix cumulé des éléments enfants)

**Piou2fois** · 13/02/2019, 17h14

Bon j'ai regardé mais ce que je souhaite faire c'est pas trouvé les parents et remonter mais le contraire
Avoir un ID d'un élément, et récupérer les enfants (ça c'est simple) mais surtout par récursivité faire des calculs depuis le plus bas niveau et remonter pour avoir un prix
Voici mon jeux de données simple

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
 
CREATE TABLE [dbo].[TESTDEVIS](
	[DVS_ID] [int] IDENTITY(1,1) NOT NULL,
	[DVS_PARENT_ID] [int] NULL,
	[DVS_DESCRIPTION] [nvarchar](50) NOT NULL,
	[DVS_UNIT_PRICE] [decimal](11, 2) NULL,
	[DVS_QUANTITY] [int] NULL,
 CONSTRAINT [PK_TESTDEVIS] PRIMARY KEY CLUSTERED 
(
	[DVS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TESTDEVIS] ON 
GO
INSERT [dbo].[TESTDEVIS] ([DVS_ID], [DVS_PARENT_ID], [DVS_DESCRIPTION], [DVS_UNIT_PRICE], [DVS_QUANTITY]) VALUES (1, NULL, N'Machine 1', NULL, NULL)
GO
INSERT [dbo].[TESTDEVIS] ([DVS_ID], [DVS_PARENT_ID], [DVS_DESCRIPTION], [DVS_UNIT_PRICE], [DVS_QUANTITY]) VALUES (2, NULL, N'Machine 2', NULL, NULL)
GO
INSERT [dbo].[TESTDEVIS] ([DVS_ID], [DVS_PARENT_ID], [DVS_DESCRIPTION], [DVS_UNIT_PRICE], [DVS_QUANTITY]) VALUES (3, 1, N'Moteur', CAST(200.00 AS Decimal(11, 2)), 1)
GO
INSERT [dbo].[TESTDEVIS] ([DVS_ID], [DVS_PARENT_ID], [DVS_DESCRIPTION], [DVS_UNIT_PRICE], [DVS_QUANTITY]) VALUES (4, 1, N'balancier', NULL, 6)
GO
INSERT [dbo].[TESTDEVIS] ([DVS_ID], [DVS_PARENT_ID], [DVS_DESCRIPTION], [DVS_UNIT_PRICE], [DVS_QUANTITY]) VALUES (5, 4, N'vérin', CAST(100.00 AS Decimal(11, 2)), 1)
GO
INSERT [dbo].[TESTDEVIS] ([DVS_ID], [DVS_PARENT_ID], [DVS_DESCRIPTION], [DVS_UNIT_PRICE], [DVS_QUANTITY]) VALUES (6, 4, N'plaque', CAST(50.00 AS Decimal(11, 2)), 2)
GO
INSERT [dbo].[TESTDEVIS] ([DVS_ID], [DVS_PARENT_ID], [DVS_DESCRIPTION], [DVS_UNIT_PRICE], [DVS_QUANTITY]) VALUES (7, 2, N'Moteur', CAST(200.00 AS Decimal(11, 2)), 1)
GO
INSERT [dbo].[TESTDEVIS] ([DVS_ID], [DVS_PARENT_ID], [DVS_DESCRIPTION], [DVS_UNIT_PRICE], [DVS_QUANTITY]) VALUES (8, 2, N'balancier', NULL, 6)
GO
INSERT [dbo].[TESTDEVIS] ([DVS_ID], [DVS_PARENT_ID], [DVS_DESCRIPTION], [DVS_UNIT_PRICE], [DVS_QUANTITY]) VALUES (9, 8, N'vérin', CAST(100.00 AS Decimal(11, 2)), 1)
GO
INSERT [dbo].[TESTDEVIS] ([DVS_ID], [DVS_PARENT_ID], [DVS_DESCRIPTION], [DVS_UNIT_PRICE], [DVS_QUANTITY]) VALUES (10, 8, N'plaque', CAST(50.00 AS Decimal(11, 2)), 2)
GO
SET IDENTITY_INSERT [dbo].[TESTDEVIS] OFF
GO

J'aimerai en fait avoir le prix de la machine 1 par exemple indépendamment du nombre d'enfants
La machine 1 a:
-1 moteur à 200€
-6 balanciers qui contiennent 1 vérin à 100€ et 2 plaque à 50€
Je devrais avoir le total de 1400€
Si en plus je peux avoir les calculs intermédiaires c'est top

**sebastien_m** · 13/02/2019, 17h26

voilà une requête qui montre le début de ce que l'on peut faire.

Je dois bouger, pas trop le temps pour l'instant je complèterai demain matin si besoin. Je n'ai pas pu la tester, mais le principe doit être correct.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH LISTE as (
		SELECT DVS_ID, DVS_PARENT_ID, DVS_UNIT_PRICE, DVS_QUANTITY, DVS_QUANTITY as QTE_MULTINIV
		FROM TESTDEVIS
		WHERE DVS_ID = 1
		UNION ALL
 
		SELECT D.ID, D.DVS_PARENT_ID, D.DVS_UNIT_PRICE, D.DVS_QUANTITY, LISTE.QTE_MULTINIV * D.DVS_QUANTITY as QTE_MULTINIV
		FROM LISTE
		INNER JOIN TESTDEVIS D ON D.DVS_PARENT_ID = LISTE.DVS_ID
)
SELECT * FROM LISTE

il y aura peut être besoin de faire des CAST pour forcer le type de QTE_MULTI_NIV.

**Piou2fois** · 13/02/2019, 17h43

Ok
Faut juste modifier la quantité Null dans les premiers niveaux (machine 1 et machine 2)

Code :

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

1
2
 
SELECT SUM(LISTE.QTE_MULTINIV * LISTE.DVS_UNIT_PRICE ) FROM LISTE

Faut juste que je regarde à passer à NULL le DVS_UNIT_PRICE si l'objet a des enfants) et normalement ça devrait fonctionner

**Piou2fois** · 14/02/2019, 10h46

Bonjour
J'ai réfléchi et je me suis dit que je pouvais peut être faire un truc sympa et simple : mettre à jour en remontant les prix
Donc j'ai créé le déclencheur suivant :

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 TRIGGER dbo.TRG_TESTDEVIS 
   ON  dbo.TESTDEVIS
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
	UPDATE dbo.TESTDEVIS
	SET DVS_UNIT_PRICE = (SELECT SUM(T2.DVS_UNIT_PRICE * T2.DVS_QUANTITY) FROM TESTDEVIS T2 WHERE T2.DVS_PARENT_ID = (SELECT DVS_PARENT_ID FROM DELETED))
	WHERE DVS_ID = (SELECT DVS_PARENT_ID FROM DELETED)
END

Ca marche pour le premier niveau mais le trigger ne se déclenche pas pour ce qui a été modifié via le UPDATE
J'imagine qu'il y a une option à mettre dans le trigger pour qu'il s'autodéclenche non ? (sachant que pour le coût c'est ascendant, je risque pas de créer des boucles infinies normalement)
D'avance merci

**Piou2fois** · 14/02/2019, 10h59

Code :

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

1
2
 
SET RECURSIVE_TRIGGERS ON;

J'ai activé ça mais finalement il m'annonce que la profondeur est trop importante et je ne capte pas car à un moment il n'y a plus de modifications possibles normalement où sinon je me trompe quelque part dans le trigger (j'ai testé un par un et ça fonctionne sans le récursif)

**sebastien_m** · 14/02/2019, 11h29

pourquoi stocker la somme des niveaux inférieurs alors que tu peux l'obtenir par requête ?

La redondance d'information c'est pas bien !

Les triggers, tant qu'on peut éviter, c'est pas plus mal.

**Piou2fois** · 14/02/2019, 14h54

le soucis c'est que si la personne met par exemple "ensemble 1" et indique un tarif mais qu'au final il se dit mince je vais le détailler, là pour le coup je vais avoir un soucis car le coût sera indiqué alors qu'il devrait être calculé, ça veut dire que dans ma requête il va falloir que je gère la suppression du prix.

**sebastien_m** · 14/02/2019, 15h21

là, ça va devenir un peu compliqué de t'aider : on ne sait pas où tu veux en venir niveau métier.
Quelques questions me viennent en tête :
Au moment où l'utilisateur crée la ligne de niveau 0 avec 'ENSEMBLE 1', les composants sont ils déjà connus ?
lorsque la ligne est présente, les seuls prix indiqués et modifiables sont ceux des matières ? ça risque d'être fastidieux s'il y a beaucoup de composants.

**Piou2fois** · 14/02/2019, 15h38

Je souhaite faire une système de devis multi niveaux
En gros quand on ajoute une ligne, on est pas sur qu'elle va avoir des enfants, ça va dépendre du désir de l'utilisateur d'aller dans le détail ou pas, mais parfois on peut mettre la ligne mais on attends les devis des fournisseurs pour complèter
Actuellement j'ai un système à 1 niveau seulement, c'est pas flexible et parfois ça bloque un peu la créativité

Ce système me permettrait aussi de faire des catégories du genre :
Projet 1
-Pré-étude
-Etude
-Fabrication
--Achat Moteur
--Achat pièces usinées
--Montage
--Réception
-Mise en caisse
-Mise en service
--Déplacement
--Travail

En gros c'est un système de nomenclature avec les prix. Sauf que je ne connais pas la profondeur de la nomenclature et je ne peux pas être qu'un item aura ou n'aura pas d'enfants
D'où le trigger qui me semblait être une bonne idée, pas besoin de calcul etc.

Je sors de la demande initiale, mais c'est en continuité finalement

**sebastien_m** · 14/02/2019, 15h50

d'après ce que je comprends, voilà ce que je ferais :
je rendrais saisissable le prix d'une ligne si elle n'a pas de filles (le prix des matières quoi).
si un utilisateur transforme une matière en semi-fini, alors je forcerais à zéro le prix de la ligne concernée (son prix serait la somme des prix de ses composants).

Par contre, j'essaierais d'esquiver les triggers. Je me débrouillerais pour faire une requête qui me calcule les sommes pour chaque sous-niveau afin de l'afficher.

A la limite, je prévoirais une requête en fin de saisie qui vient mettre à jour les prix calculés sur les semi-finis.

Il y a surement d'autres points de vue sur le sujet qui s'exprimeront peut-être ici.

Quoi qu'il en soit, avec le multi-niveau, tu mets le doigt dans quelque-chose d'assez complexe.

Système de configuration de devis multi niveaux

Développement SQL Server

Discussions similaires

Partager

Partager