Remettre à zéro un Dolibarr : vider les données de test sans laisser d'orphelins

# Sauvegarde de la base (sur MariaDB récent, la commande s'appelle mariadb-dump)
mysqldump -u utilisateur -p --single-transaction --default-character-set=utf8mb4 nom_de_la_base > dolibarr-avant-remise-a-zero.sql
# Sauvegarde du dossier des documents (ici /var/www/documents)
tar czf documents-avant-remise-a-zero.tar.gz -C /var/www documents
# Retour arrière si besoin
mysql -u utilisateur -p nom_de_la_base < dolibarr-avant-remise-a-zero.sql

-- remise-a-zero.sql : documents commerciaux, paiements et stock (tiers, produits et réglages conservés)
START TRANSACTION;

-- 1. Écritures bancaires créées par les paiements de factures (clients et fournisseurs)
DELETE FROM llx_category_bankline WHERE lineid IN (SELECT fk_bank FROM llx_bank_url WHERE type IN ('payment', 'payment_supplier'));
DELETE FROM llx_bank WHERE rowid IN (SELECT fk_bank FROM llx_bank_url WHERE type IN ('payment', 'payment_supplier'));
DELETE FROM llx_bank_url WHERE fk_bank NOT IN (SELECT rowid FROM llx_bank);

-- 2. Paiements, avoirs et remises disponibles
DELETE FROM llx_paiement_facture;
DELETE FROM llx_paiement;
DELETE FROM llx_paiement_extrafields;
DELETE FROM llx_paiementfourn_facturefourn;
DELETE FROM llx_paiementfourn;
DELETE FROM llx_societe_remise_except;

-- 3. Catégories posées sur les documents
DELETE FROM llx_categorie_propal;
DELETE FROM llx_categorie_order;
DELETE FROM llx_categorie_invoice;
DELETE FROM llx_categorie_supplier_proposal;
DELETE FROM llx_categorie_supplier_order;
DELETE FROM llx_categorie_supplier_invoice;

-- 4. Ventes : expéditions, bons de livraison, factures, commandes, devis
DELETE FROM llx_expeditiondet_batch;
DELETE FROM llx_expeditiondet;
DELETE FROM llx_expedition;
DELETE FROM llx_deliverydet;
DELETE FROM llx_delivery;
DELETE FROM llx_facturedet;
DELETE FROM llx_facture;
DELETE FROM llx_facturedet_rec;
DELETE FROM llx_facture_rec;
DELETE FROM llx_commandedet;
DELETE FROM llx_commande;
DELETE FROM llx_propaldet;
DELETE FROM llx_propal;
DELETE FROM llx_propal_extrafields; DELETE FROM llx_propaldet_extrafields;
DELETE FROM llx_commande_extrafields; DELETE FROM llx_commandedet_extrafields;
DELETE FROM llx_expedition_extrafields; DELETE FROM llx_expeditiondet_extrafields;
DELETE FROM llx_delivery_extrafields; DELETE FROM llx_deliverydet_extrafields;
DELETE FROM llx_facture_extrafields; DELETE FROM llx_facturedet_extrafields;
DELETE FROM llx_facture_rec_extrafields; DELETE FROM llx_facturedet_rec_extrafields;

-- 5. Achats : réceptions, factures, commandes et demandes de prix fournisseur
DELETE FROM llx_receptiondet_batch;
DELETE FROM llx_reception;
DELETE FROM llx_facture_fourn_det;
DELETE FROM llx_facture_fourn;
DELETE FROM llx_facture_fourn_det_rec;
DELETE FROM llx_facture_fourn_rec;
DELETE FROM llx_commande_fournisseur_log;
DELETE FROM llx_commande_fournisseurdet;
DELETE FROM llx_commande_fournisseur;
DELETE FROM llx_supplier_proposaldet;
DELETE FROM llx_supplier_proposal;
DELETE FROM llx_reception_extrafields; DELETE FROM llx_receptiondet_batch_extrafields;
DELETE FROM llx_facture_fourn_extrafields; DELETE FROM llx_facture_fourn_det_extrafields;
DELETE FROM llx_facture_fourn_rec_extrafields; DELETE FROM llx_facture_fourn_det_rec_extrafields;
DELETE FROM llx_commande_fournisseur_extrafields; DELETE FROM llx_commande_fournisseurdet_extrafields;
DELETE FROM llx_supplier_proposal_extrafields; DELETE FROM llx_supplier_proposaldet_extrafields;

-- 6. Liens, contacts, événements et index des fichiers qui pointaient vers ces documents
DELETE FROM llx_element_element
WHERE sourcetype IN ('propal', 'commande', 'shipping', 'delivery', 'facture', 'supplier_proposal', 'order_supplier', 'reception', 'invoice_supplier', 'facturerec', 'invoice_supplier_rec')
   OR targettype IN ('propal', 'commande', 'shipping', 'delivery', 'facture', 'supplier_proposal', 'order_supplier', 'reception', 'invoice_supplier', 'facturerec', 'invoice_supplier_rec');
DELETE ec FROM llx_element_contact AS ec
JOIN llx_c_type_contact AS tc ON tc.rowid = ec.fk_c_type_contact
WHERE tc.element IN ('propal', 'commande', 'shipping', 'facture', 'supplier_proposal', 'order_supplier', 'invoice_supplier');
DELETE r FROM llx_actioncomm_resources AS r
JOIN llx_actioncomm AS a ON a.id = r.fk_actioncomm
WHERE a.elementtype IN ('propal', 'order', 'shipping', 'invoice', 'supplier_proposal', 'order_supplier', 'invoice_supplier', 'reception', 'facturerec');
DELETE FROM llx_actioncomm
WHERE elementtype IN ('propal', 'order', 'shipping', 'invoice', 'supplier_proposal', 'order_supplier', 'invoice_supplier', 'reception', 'facturerec');
DELETE FROM llx_ecm_files
WHERE filepath LIKE 'propale/%' OR filepath LIKE 'commande/%' OR filepath LIKE 'expedition/%'
   OR filepath LIKE 'facture/%' OR filepath LIKE 'fournisseur/%' OR filepath LIKE 'supplier_proposal/%'
   OR filepath LIKE 'reception/%';

-- 7. Stock : inventaires, mouvements, stock par entrepôt et par lot
DELETE FROM llx_inventorydet;
DELETE FROM llx_inventory;
UPDATE llx_mrp_production SET fk_stock_movement = NULL;
DELETE FROM llx_stock_mouvement;
DELETE FROM llx_stock_mouvement_extrafields; DELETE FROM llx_inventory_extrafields;
DELETE FROM llx_product_batch;
DELETE FROM llx_product_stock;
UPDATE llx_product AS p
SET p.stock = (SELECT SUM(ps.reel) FROM llx_product_stock AS ps WHERE ps.fk_product = p.rowid),
    p.pmp = 0;

COMMIT;

-- remise-a-zero-entrepot.sql : un seul entrepôt, désigné par sa référence
START TRANSACTION;
UPDATE llx_mrp_production AS mp
JOIN llx_stock_mouvement AS m ON m.rowid = mp.fk_stock_movement
JOIN llx_entrepot AS w ON w.rowid = m.fk_entrepot
SET mp.fk_stock_movement = NULL
WHERE w.ref = 'WAREHOUSEPARIS';
DELETE m FROM llx_stock_mouvement AS m
JOIN llx_entrepot AS w ON w.rowid = m.fk_entrepot
WHERE w.ref = 'WAREHOUSEPARIS';
DELETE b FROM llx_product_batch AS b
JOIN llx_product_stock AS ps ON ps.rowid = b.fk_product_stock
JOIN llx_entrepot AS w ON w.rowid = ps.fk_entrepot
WHERE w.ref = 'WAREHOUSEPARIS';
DELETE ps FROM llx_product_stock AS ps
JOIN llx_entrepot AS w ON w.rowid = ps.fk_entrepot
WHERE w.ref = 'WAREHOUSEPARIS';
DELETE e FROM llx_stock_mouvement_extrafields AS e
LEFT JOIN llx_stock_mouvement AS m ON m.rowid = e.fk_object
WHERE m.rowid IS NULL;
UPDATE llx_product AS p
SET p.stock = (SELECT SUM(ps.reel) FROM llx_product_stock AS ps WHERE ps.fk_product = p.rowid);
COMMIT;

cd /var/www/documents
rm -rf propale/* commande/* expedition/sending/* expedition/receipt/* facture/* fournisseur/commande/* fournisseur/facture/* fournisseur/payment/* supplier_proposal/* reception/*

SELECT 'factures' AS reste, COUNT(*) AS n FROM llx_facture
UNION ALL SELECT 'factures fournisseur', COUNT(*) FROM llx_facture_fourn
UNION ALL SELECT 'liens paiement / banque', COUNT(*) FROM llx_bank_url WHERE type IN ('payment', 'payment_supplier')
UNION ALL SELECT 'liens entre documents', COUNT(*) FROM llx_element_element WHERE sourcetype IN ('propal', 'commande', 'facture', 'shipping', 'order_supplier') OR targettype IN ('propal', 'commande', 'facture', 'shipping', 'order_supplier')
UNION ALL SELECT 'mouvements de stock', COUNT(*) FROM llx_stock_mouvement
UNION ALL SELECT 'produits avec un stock', COUNT(*) FROM llx_product WHERE stock IS NOT NULL;