USE temp ;
DROP TABLE IF EXISTS PILE ;
DROP TABLE IF EXISTS PARRAINAGE ;
DROP TABLE IF EXISTS ACHAT ;
DROP TABLE IF EXISTS MEMBRE ;
DROP TABLE IF EXISTS PACK ;
DROP TABLE IF EXISTS POURCENTAGE ;
CREATE TABLE POURCENTAGE
(
Niveau INT NOT NULL
, Pourcentage DECIMAL(5,2) NOT NULL
) ;
INSERT INTO POURCENTAGE VALUES (1, 0.32), (2, 0.08), (3, 0.04), (4, 0.02) ;
CREATE TABLE PACK
(
PackId INT NOT NULL
, Souscription INT NOT NULL
, CONSTRAINT PACK_PK PRIMARY KEY (PackId)
) ;
INSERT INTO PACK VALUES (1, 250), (2, 149) ;
CREATE TABLE MEMBRE
(
MembreId INT NOT NULL
, MembreCode CHAR(11) NOT NULL
, MembreNom VARCHAR(32) NOT NULL
, MembrePreNom VARCHAR(32) NOT NULL
, CONSTRAINT MEMBRE_PK PRIMARY KEY (MembreId)
, CONSTRAINT MEMBRE_CODE_AK UNIQUE (MembreCode)
) ;
CREATE TABLE ACHAT
(
MembreId INT NOT NULL
, PackId INT NOT NULL
, AchatDate DATE NOT NULL DEFAULT '2015-10-22'
, CONSTRAINT ACHAT_PK PRIMARY KEY (MembreId, PackId)
, CONSTRAINT ACHAT_MEMBRE_FK FOREIGN KEY (MembreId)
REFERENCES MEMBRE (MembreId)
, CONSTRAINT ACHAT_PACK_FK FOREIGN KEY (PackId)
REFERENCES PACK (PackId)
) ;
CREATE TABLE PARRAINAGE
(
MembreId INT NOT NULL
, PackId INT NOT NULL DEFAULT 1
, ParrainId INT NOT NULL
, DateAdd DATE NOT NULL DEFAULT '2015-10-22'
, CONSTRAINT PARRAINAGE_PK PRIMARY KEY (MembreId)
, CONSTRAINT PARRAINAGE_FILLEUIL_FK FOREIGN KEY (MembreId, PackId)
REFERENCES ACHAT (MembreId, PackId)
, CONSTRAINT PARRAINAGE_PARRAIN_FK FOREIGN KEY (ParrainId, PackId)
REFERENCES ACHAT (MembreId, PackId)
-- , CONSTRAINT PARRAINAGE_CHK01 CHECK (MembreId <> ParrainId)
) ;
-- --
---------------------------------------------------
-- TABLE PILE - TABLE de travail
-- -----------------------------------------------------
CREATE TABLE PILE
(
PileId INT NOT NULL AUTO_INCREMENT,
Niveau INT NOT NULL,
MembreId INT NOT NULL,
PackId INT NOT NULL,
ParrainId INT NOT NULL DEFAULT 0,
Gain DECIMAL(5,2) NOT NULL
, CONSTRAINT PILE_PK PRIMARY KEY (PileId)
, CONSTRAINT PILE_AK UNIQUE (MembreId, PackId)
) ;
-- ------------------------------------------------------------------------------
INSERT INTO MEMBRE (MembreId, MembreCode, MembreNom, MembrePreNom) VALUES
(1, 'FR-NAUF-456', 'Naudin', 'Fernand')
, (2, 'FR-VOLR-258', 'Volfoni', 'Raoul')
, (3, 'FR-VOLP-871', 'Volfoni', 'Paul')
, (4, 'FR-DELA-051', 'Delafoy', 'Antoine')
, (5, 'FR-DELA-983', 'Delafoy', 'Adolphe')
, (6, 'BE-HADA-589', 'Haddock', 'Archibald')
, (7, 'FR-LERR-982', 'Le Rouge', 'Rackham')
, (8, 'FR-FILA-981', 'Filoselle', 'Aristide')
, (9, 'IT-CASB-222', 'Castafiore', 'Bianca')
, (10, 'BE-HADF-278', 'de Hadoque', 'François')
, (11, 'GR-RASR-259', 'Rastapopoulos', 'Roberto')
, (12, 'BE-TOUT-047', 'Tournesol', 'Tryphon')
, (13, 'BE-LAMS-726', 'Lampion', 'Séraphin')
, (14, 'FR-BERH-189', 'Bergamotte', 'Hippolyte')
, (15, 'SU-BJOE-358', 'Björgenskjöld', 'Erik')
, (16, 'SP-BOLP-146', 'Bolero y Calamares', 'Porfirio')
, (17, 'BE-BOUI-257', 'Boullu', 'Isidore')
, (18, 'JP-BUNK-236', 'Bunji', 'Kuraki')
, (19, 'BE-CALH-147', 'Calys', 'Hippolyte')
, (20, 'SP-BADR-123', 'Bada', 'Ramon')
, (21, 'BE-HALN-457', 'Halambique', 'Nestor')
, (22, 'BE-HALA-250', 'Halambique', 'Alfred')
, (23, 'FR-CANP-012', 'Cantonneau', 'Paul')
, (24, 'RU-SAKI-345', 'Sakharine', 'Ivan Ivanovitch')
, (25, 'HU-CARL-678', 'Carreidas', 'Laszlo')
, (26, 'GE-SCHO-890', 'Schulze', 'Otto')
, (27, 'FR-SICP-014', 'Siclone', 'Philémon')
, (28, 'US-SMIB-987', 'Smiles', 'Bobby')
, (29, 'RU-SOLD-876', 'Solowztenxopztzki', 'Dimitrieff')
, (30, 'GR-PAPT-765', 'Paparanic', 'Thémistocle')
, (31, 'SP-TORR-654', 'Tortilla', 'Rodrigo')
, (32, 'BE-TRIE-321', 'Triboulet', 'Eugène')
, (33, 'BE-VANE-210', 'Vanneau', 'Émile')
, (34, 'US-DELA-098', 'Mac Adam', 'Mike')
, (35, 'BE-MACM-980', 'Loiseau', 'Maxime')
, (36, 'BE-VANM-883', 'Vandezande', 'Martine')
, (37, 'FR-DUGC-290', 'Dugommier', 'Claude')
, (38, 'FR-TRAJ-291', 'Trencène', 'Jean')
, (41, 'PO-DOSP-419', 'Dos Santos', 'Pedro Joãs')
, (42, 'FR-RIZW-428', 'Rizotto', 'Walter')
, (43, 'GE-BOEH-341', 'Boehm', 'Hans')
, (44, 'PO-DAFO-884', 'Da Figueira', 'Oliveira')
, (45, 'SP-PERA-789', 'Perez', 'Alonzo')
, (46, 'BE-HART-642', 'D''Hartimont', 'Thomas')
, (47, 'FR-EBAA-247', 'Ébasque', 'Albert')
, (48, 'FR-EULI-248', 'Eulebion', 'Igor')
, (51, 'RU-BAZB-725', 'Bazaroff', 'Basil')
, (52, 'IR-OCOM-152', 'O''Connor', 'Mac')
, (53, 'FR-LEGZ-252', 'Legras', 'Zorbec')
;
INSERT INTO ACHAT (PackId, MembreId) VALUES
(1, 1)
, (1, 11), (1, 12), (1, 13)
, (1, 21), (1, 22), (1, 23), (1, 24), (1, 25)
, (1, 31), (1, 32), (1, 33), (1, 34), (1, 35), (1, 36)
, (1, 41), (1, 42), (1, 43), (1, 44), (1, 45), (1, 46)
, (1, 51), (1, 52)
, (2, 1)
, (2, 14), (2, 15)
, (2, 26), (2, 27)
, (2, 37)
, (2, 47), (2, 48)
, (2, 53)
;
INSERT INTO PARRAINAGE (MembreId, PackId, ParrainId) VALUES
(11, 1, 1), (12, 1, 1), (13, 1, 1)
, (21, 1, 11), (22, 1, 11), (23, 1, 11), (24, 1, 12), (25, 1, 13)
, (31, 1, 21), (32, 1, 22), (33, 1, 22), (34, 1, 22), (35, 1, 25), (36, 1, 25)
, (41, 1, 31), (42, 1, 31), (43, 1, 34), (44, 1, 34), (45, 1, 36), (46, 1, 36)
, (51, 1, 43), (52, 1, 43)
, (14, 2, 1), (15, 2, 1)
, (26, 2, 14), (27, 2, 14)
, (37, 2, 26)
, (47, 2, 37), (48, 2, 37)
, (53, 2, 48)
;
-- ------------------------------------------------------------------------------------------------------------
DELIMITER GO
DROP FUNCTION IF EXISTS CalculGainTotalMembre
GO
CREATE FUNCTION CalculGainTotalMembre (LeMembreCode CHAR(11))
RETURNS DECIMAL(5,2)
BEGIN
DECLARE theGain INT default 0 ;
DELETE FROM PILE ;
CALL RecursonsJoyeusement(true, LeMembreCode, theGain) ;
RETURN (SELECT SUM(Gain) FROM PILE) ;
END
GO
DROP PROCEDURE IF EXISTS RecursonsJoyeusement
GO
CREATE PROCEDURE RecursonsJoyeusement
(
IN Amorce BOOLEAN, LeMembreCode CHAR(11)
, INOUT GainTotal DECIMAL(5,2)
)
BEGIN
DECLARE theNiveau INT DEFAULT 0;
DECLARE theKount INT ;
SET theNiveau = (SELECT DISTINCT COALESCE(MAX(Niveau) + 1, 1) FROM PILE) ;
IF Amorce = true
THEN
INSERT INTO PILE (MembreId, PackId, ParrainId, Niveau, Gain)
SELECT x.MembreId, x.PackId, x.ParrainId, 1, z.Souscription * (SELECT POURCENTAGE FROM POURCENTAGE WHERE Niveau = 1)
FROM PARRAINAGE AS x JOIN ACHAT AS y ON x.MembreId = y.MembreId AND x.PackId = y.PackId
JOIN PACK AS z ON y.PackId = z.PackId
JOIN MEMBRE AS u ON x.ParrainId = u.MembreId
WHERE u.MembreCode = LeMembreCode ;
CALL RecursonsJoyeusement(false, '', GainTotal) ;
ELSE
SET theKount = (SELECT COUNT(*)
FROM PARRAINAGE AS x JOIN PILE AS y ON x.ParrainId = y.MembreId
WHERE Niveau = TheNiveau - 1) ;
IF theKount > 0 THEN
INSERT INTO PILE (MembreId, PackId, ParrainId, Niveau, Gain)
SELECT y.MembreId, x.PackId, y.ParrainId, x.Niveau + 1
, CASE x.Niveau + 1
WHEN 2 THEN t.Souscription * (SELECT POURCENTAGE FROM POURCENTAGE WHERE Niveau = 2)
WHEN 3 THEN t.Souscription * (SELECT POURCENTAGE FROM POURCENTAGE WHERE Niveau = 3)
WHEN 4 THEN t.Souscription * (SELECT POURCENTAGE FROM POURCENTAGE WHERE Niveau = 4)
ELSE 0
END
FROM PILE AS x JOIN PARRAINAGE AS y ON x.MembreId = y.ParrainId AND x.PackId = y.PackId
JOIN ACHAT AS z ON y.MembreId = z.MembreId AND y.PackId = z.PackId
JOIN PACK AS t ON z.PackId = t.PackId
WHERE x.Niveau = theNiveau - 1 ;
CALL RecursonsJoyeusement(false, '', GainTotal) ;
END IF ;
END IF;
-- Récup des gains de Cézigue dans la pile
SET GainTotal = (SELECT SUM(Gain) FROM PILE) ;
END
GO
DELIMITER ;
-- ------------------------------------------------
-- Appel à la fonction CalculGainTotalMembre
-- ------------------------------------------------
SELECT CalculGainTotalMembre('FR-NAUF-456') ;
Système de parrainage MLM
Répondre avec citation
. Dans le cas de MySQL, pas de problème, j’ai une procédure au frigo... 
, y t'il une solution dans mon cas
Envoyé par Mat.M
LIVRES :
Partager