You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
76 lines
4.3 KiB
SQL
76 lines
4.3 KiB
SQL
-- Seed data for stocks
|
|
-- Populate stock quantities for each tool in different warehouses
|
|
-- Note: quantite_disponible should be calculated dynamically, but we're storing it (BUG-010)
|
|
|
|
-- Function to generate random stock distribution (manually executed)
|
|
|
|
-- Perceuse Bosch (id=1) - popular tool, available in most warehouses
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible) VALUES
|
|
(1, 1, 5, 4), (1, 2, 3, 3), (1, 3, 4, 2), (1, 4, 3, 3), (1, 5, 2, 2), (1, 6, 3, 3), (1, 7, 4, 4), (1, 8, 2, 2), (1, 9, 3, 3), (1, 10, 2, 1);
|
|
|
|
-- Visseuse Makita (id=2)
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible) VALUES
|
|
(2, 1, 4, 3), (2, 2, 5, 4), (2, 3, 3, 3), (2, 4, 4, 2), (2, 5, 3, 3), (2, 6, 2, 2), (2, 7, 3, 3), (2, 8, 4, 4), (2, 9, 2, 2), (2, 10, 3, 3);
|
|
|
|
-- Perceuse-visseuse DeWalt (id=3)
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible) VALUES
|
|
(3, 1, 3, 2), (3, 2, 2, 2), (3, 3, 4, 3), (3, 4, 3, 3), (3, 5, 2, 1), (3, 6, 3, 3), (3, 7, 2, 2), (3, 8, 3, 3), (3, 9, 4, 4), (3, 10, 2, 2);
|
|
|
|
-- Ponceuse Festool (id=4)
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible) VALUES
|
|
(4, 1, 2, 2), (4, 2, 3, 2), (4, 3, 2, 2), (4, 4, 3, 3), (4, 5, 2, 2), (4, 6, 2, 1), (4, 7, 3, 3), (4, 8, 2, 2), (4, 9, 2, 2), (4, 10, 3, 3);
|
|
|
|
-- Ponceuse à bande Makita (id=5)
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible) VALUES
|
|
(5, 1, 3, 3), (5, 2, 2, 2), (5, 3, 3, 2), (5, 4, 2, 2), (5, 5, 3, 3), (5, 6, 2, 2), (5, 7, 2, 2), (5, 8, 3, 3), (5, 9, 2, 2), (5, 10, 2, 1);
|
|
|
|
-- Continue with remaining tools (simplified for brevity - in production would have full data)
|
|
-- Adding stock for all 50+ tools across 10 warehouses
|
|
|
|
-- Ponceuse orbitale Bosch (id=6)
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible) VALUES
|
|
(6, 1, 4, 4), (6, 2, 3, 3), (6, 3, 4, 3), (6, 4, 3, 3), (6, 5, 2, 2), (6, 6, 3, 3), (6, 7, 4, 4), (6, 8, 2, 2), (6, 9, 3, 3), (6, 10, 2, 2);
|
|
|
|
-- Scie circulaire Makita (id=7)
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible) VALUES
|
|
(7, 1, 3, 2), (7, 2, 4, 4), (7, 3, 2, 2), (7, 4, 3, 2), (7, 5, 3, 3), (7, 6, 2, 2), (7, 7, 3, 3), (7, 8, 4, 4), (7, 9, 2, 1), (7, 10, 3, 3);
|
|
|
|
-- Scie sauteuse Bosch (id=8)
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible) VALUES
|
|
(8, 1, 5, 4), (8, 2, 3, 3), (8, 3, 4, 4), (8, 4, 3, 3), (8, 5, 4, 4), (8, 6, 3, 2), (8, 7, 2, 2), (8, 8, 3, 3), (8, 9, 4, 4), (8, 10, 3, 3);
|
|
|
|
-- Add minimal stock for remaining tools (ids 9-50+)
|
|
-- In a real scenario, all tools would have full stock data
|
|
|
|
-- For tools 9-20 (Scies, Meuleuses, Marteaux)
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible)
|
|
SELECT outil_id, entrepot_id,
|
|
CASE WHEN outil_id % 3 = 0 THEN 4 WHEN outil_id % 3 = 1 THEN 3 ELSE 2 END as quantite_totale,
|
|
CASE WHEN outil_id % 3 = 0 THEN 3 WHEN outil_id % 3 = 1 THEN 2 ELSE 2 END as quantite_disponible
|
|
FROM (SELECT id as outil_id FROM outils WHERE id BETWEEN 9 AND 20) o
|
|
CROSS JOIN (SELECT id as entrepot_id FROM entrepots) e;
|
|
|
|
-- For tools 21-30 (Jardinage)
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible)
|
|
SELECT outil_id, entrepot_id,
|
|
CASE WHEN outil_id % 2 = 0 THEN 2 ELSE 3 END as quantite_totale,
|
|
CASE WHEN outil_id % 2 = 0 THEN 1 ELSE 2 END as quantite_disponible
|
|
FROM (SELECT id as outil_id FROM outils WHERE id BETWEEN 21 AND 30) o
|
|
CROSS JOIN (SELECT id as entrepot_id FROM entrepots) e;
|
|
|
|
-- For tools 31-40 (Nettoyage, Échafaudage, Peinture)
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible)
|
|
SELECT outil_id, entrepot_id,
|
|
CASE WHEN outil_id % 3 = 0 THEN 3 WHEN outil_id % 3 = 1 THEN 2 ELSE 4 END as quantite_totale,
|
|
CASE WHEN outil_id % 3 = 0 THEN 2 WHEN outil_id % 3 = 1 THEN 2 ELSE 3 END as quantite_disponible
|
|
FROM (SELECT id as outil_id FROM outils WHERE id BETWEEN 31 AND 40) o
|
|
CROSS JOIN (SELECT id as entrepot_id FROM entrepots) e;
|
|
|
|
-- For remaining tools (41+)
|
|
INSERT INTO stocks (outil_id, entrepot_id, quantite_totale, quantite_disponible)
|
|
SELECT outil_id, entrepot_id,
|
|
CASE WHEN outil_id % 2 = 0 THEN 3 ELSE 2 END as quantite_totale,
|
|
CASE WHEN outil_id % 2 = 0 THEN 2 ELSE 2 END as quantite_disponible
|
|
FROM (SELECT id as outil_id FROM outils WHERE id > 40) o
|
|
CROSS JOIN (SELECT id as entrepot_id FROM entrepots) e;
|