# Test Data Removal Fix - Dolibarr CRM

## Problem Description

When attempting to delete records (Customers, Products, Invoices, etc.) through the Dolibarr web interface, the system displays the following error:

```
Failed to delete record since it has some child records.
Object [Name] has at least one child of type Invoice
```

This error occurs because Dolibarr enforces referential integrity - records cannot be deleted if they have related child records (invoices, orders, proposals, payments, etc.).

## Root Cause

The initial cleanup script only removed base data (customers, products, warehouses) but did not remove the related transactional data that references these records:
- Invoices (facture)
- Invoice details (facture_det)
- Orders (commande)
- Order details (commande_det)
- Proposals (propal)
- Proposal details (propaldet)
- Supplier invoices (facture_fourn)
- Payments (paiement)
- Stock movements (stock_mouvement)

## Solution

A comprehensive cleanup script was created that removes ALL related data in the correct order to avoid foreign key constraint violations.

## Location

All scripts are located in: `c:\xampp\htdocs\admin_crm\dev\tools\`

### Available Scripts

1. **remove_test_data_auto.php** - Automated version (recommended, no confirmation required)
2. **remove_test_data_standalone.php** - Interactive version (requires confirmation)
3. **verify_cleanup.php** - Verification script to confirm data removal
4. **check_data.php** - Diagnostic script to check current data counts

## Step-by-Step Instructions

### Step 1: Open Command Prompt/Termal

Press `Windows + R`, type `cmd`, and press Enter.

### Step 2: Navigate to Tools Directory

```bash
cd c:\xampp\htdocs\admin_crm\dev\tools
```

### Step 3: Check Current Data (Optional)

Before removing data, you can check what exists:

```bash
php check_data.php
```

This will display the count of records in each table.

### Step 4: Run the Cleanup Script

Execute the automated cleanup script:

```bash
php remove_test_data_auto.php
```

### **What this script does:**
- Connects to your database (democrm)
- Displays current data counts
- Removes data in the correct order:
  1. Invoices and invoice details
  2. Supplier invoices
  3. Orders and order details
  4. Supplier orders
  5. Proposals and proposal details
  6. **Agenda events and resources**
  7. Payments and bank entries
  8. Stock movements
  9. Warehouse and stock data
  10. Products and product data
  11. Customers/Third parties and contacts
- Uses database transactions for safety
- Disables foreign key checks temporarily to avoid constraint errors

### Step 5: Verify Data Removal

After the script completes, verify that all data has been removed:

```bash
php verify_cleanup.php
```

Expected output:
```
=== Database Verification Script ===

✓ Third Parties (societe): 0 records
✓ Products (product): 0 records
✓ Warehouses (entrepot): 0 records
✓ Stock Movements (stock_mouvement): 0 records
✓ Payments (paiement): 0 records
✓ Third Parties Extrafields (societe_extrafields): 0 records
✓ Product Extrafields (product_extrafields): 0 records
✓ Product Stock (product_stock): 0 records
✓ Product Prices (product_price): 0 records
✓ Contacts (socpeople): 0 records

=== Summary ===
✓ SUCCESS! All test data has been removed.
```

### Step 6: Verify in Web Interface

1. Open your web browser
2. Navigate to: `http://localhost/admin_crm/htdocs`
3. Log in with your admin credentials
4. Check the following sections to confirm they are empty:
   - **Third Parties** → Customers/Suppliers
   - **Products/Services** → Products
   - **Stock** → Warehouses
   - **Bank/Cash** → Transactions
   - **Invoices** → Customer invoices
   - **Orders** → Customer orders
   - **Proposals** → Commercial proposals

## Database Configuration

The scripts use the following database credentials (from `htdocs/conf/conf.php`):

- **Host:** localhost
- **Database:** democrm
- **User:** root
- **Password:** (empty)
- **Table Prefix:** llx_

## What Gets Removed

### Financial Data
- Customer invoices (facture)
- Invoice line items (facture_det)
- Recurring invoices (facture_rec)
- Supplier invoices (facture_fourn)
- Supplier invoice details (facture_fourn_det)
- Payments (paiement)
- Payment-invoice links (paiement_facture)
- Bank entries (bank)
- Bank URLs (bank_url)

### Sales Data
- Customer orders (commande)
- Order details (commande_det)
- Commercial proposals (propal)
- Proposal details (propaldet)
- Supplier orders (commande_fournisseur)
- Supplier order details (commande_fournisseurdet)

### Inventory Data
- Stock movements (stock_mouvement)
- Product stock by warehouse (product_stock)
- Warehouses (entrepot)
- Product warehouse properties

### Agenda Data
- Agenda events (actioncomm)
- Agenda extrafields (actioncomm_extrafields)
- Agenda resources (actioncomm_resources)

### Product Data
- Products (product)
- Product prices (product_price)
- Product customer prices (product_customer_price)
- Product combinations
- Product extrafields (product_extrafields)
- Product categories (categorie_product)

### Customer Data
- Third parties/Customers (societe)
- Customer contacts (socpeople)
- Customer extrafields (societe_extrafields)
- Customer categories (categorie_societe)
- Customer prices (societe_prices)

## What is Preserved

The following data is NOT removed and remains intact:

- ✓ User accounts and permissions
- ✓ System configurations and constants
- ✓ Category structures (empty but available)
- ✓ Menu configurations
- ✓ Modules and module settings
- ✓ Workflows
- ✓ Email templates
- ✓ Document templates

## Troubleshooting

### Issue: Script shows "Database connection failed"

**Solution:** 
- Verify MySQL/MariaDB is running in XAMPP
- Check database credentials in `htdocs/conf/conf.php`
- Ensure database `democrm` exists

### Issue: "Table doesn't exist" errors

**Solution:** 
- These are normal and can be ignored
- Some tables may not exist if certain modules are not enabled
- The script continues with existing tables

### Issue: Foreign key constraint errors

**Solution:**
- The script automatically disables foreign key checks
- If errors persist, run: `php remove_test_data_auto.php` again
- The script uses transactions, so failed runs don't delete partial data

### Issue: Still seeing data after running script

**Solution:**
1. Clear browser cache
2. Refresh the page (Ctrl + F5)
3. Run verification script: `php verify_cleanup.php`
4. If data still exists, run cleanup script again

## Important Notes

⚠️ **WARNING:**
- This operation is **IRREVERSIBLE**
- Always backup your database before running cleanup scripts
- Only run on test/demo data, never on production data
- The script will permanently delete all specified data

💡 **Best Practices:**
- Backup database before running: Use phpMyAdmin or mysqldump
- Run verification script after cleanup
- Test in development environment first
- Document what data was removed

## Backup Before Running (Recommended)

### Option 1: Using phpMyAdmin
1. Open phpMyAdmin: `http://localhost/phpmyadmin`
2. Select database: `democrm`
3. Click "Export" tab
4. Choose "Quick" export method
5. Format: SQL
6. Click "Go"
7. Save the backup file

### Option 2: Using Command Line
```bash
cd c:\xampp\mysql\bin
mysqldump -u root democrm > c:\backup_dcrm_%date%.sql
```

## Script Technical Details

### Order of Deletion

The script deletes data in this specific order to avoid foreign key violations:

1. **Disable foreign key checks** - `SET FOREIGN_KEY_CHECKS = 0`
2. **Invoices** - Remove invoice details first, then invoices
3. **Orders** - Remove order details first, then orders
4. **Proposals** - Remove proposal details first, then proposals
5. **Agenda** - Remove agenda events, extrafields, and resources
6. **Payments** - Remove payment links, then payments, then bank entries
7. **Stock movements** - Remove all stock transaction history
8. **Stock/Warehouse** - Remove product stock, then warehouses
9. **Products** - Remove prices, extrafields, then products
10. **Customers** - Remove contacts, extrafields, then customers
11. **Commit transaction** - Save all changes
12. **Enable foreign key checks** - `SET FOREIGN_KEY_CHECKS = 1`

### Transaction Safety

The script uses database transactions:
- If ANY error occurs, ALL changes are rolled back
- No partial data deletion occurs
- Database remains in consistent state

### Error Handling

- Each table deletion is wrapped in try-catch
- Missing tables are skipped gracefully
- Errors are logged but don't stop execution
- Final summary shows what was completed

## Support

If you encounter issues not covered in this document:

1. Check error messages in terminal output
2. Verify database connection credentials
3. Ensure XAMPP MySQL service is running
4. Review Dolibarr logs in `documents/admin/`
5. Run verification script to check current state

## Version History

- **v1.0** - Initial cleanup script (limited scope)
- **v2.0** - Comprehensive cleanup with all related tables
- **v2.1** - Added invoices, orders, proposals removal
- **v2.2** - Documentation created
- **v2.3** - Added Agenda module (actioncomm) data removal

---

**Last Updated:** 2026-04-29  
**Author:** AI Assistant  
**Status:** Tested and Verified Working