ARCH-003: Consolidated reporting

[AliiiBenn]

ARCH-003: Consolidated Multi-Warehouse Reporting

Overview

Implement consolidated reporting across multiple warehouses, combining data into unified executive dashboards and operational reports.

Description

Consolidated reporting provides enterprise-wide visibility:

• Executive dashboard (all warehouses combined)
• Per-warehouse detailed reports
• Consolidated KPIs and metrics
• Multi-warehouse trend analysis
• Unified data exports

Technical Approach

Consolidated Query Pattern

def generate_consolidated_report(conn, lookback_days=30):
    """
    Generate consolidated report across all warehouses.

    Returns:
        Dict with consolidated analytics
    """
    report = {}

    # 1. Executive summary (all warehouses combined)
    report['executive_summary'] = pd.read_sql_query(f"""
        SELECT
            COUNT(DISTINCT w.id) as total_warehouses,
            COUNT(DISTINCT CONCAT(w.id, '-', p.no_produit)) as total_products,
            COUNT(m.oid) as total_movements,
            SUM(CASE WHEN m.type = 'SORTIE' THEN 1 ELSE 0 END) as total_outbound,
            ROUND(AVG(
                CASE WHEN m.type = 'SORTIE' THEN 1 ELSE 0 END
            ), 2) as global_avg_outbound_per_day,
            MIN(m.date_heure) as period_start,
            MAX(m.date_heure) as period_end
        FROM warehouses w
        LEFT JOIN produits p ON w.id = p.warehouse_id
        LEFT JOIN mouvements m ON w.id = m.warehouse_id
            AND m.date_heure >= date('now', '-{lookback_days} days')
    """, conn)

    # 2. Per-warehouse breakdown
    report['by_warehouse'] = pd.read_sql_query(f"""
        SELECT
            w.id as warehouse_id,
            w.name as warehouse_name,
            w.location,
            COUNT(DISTINCT p.no_produit) as total_products,
            COUNT(m.oid) as total_movements,
            SUM(CASE WHEN m.type = 'SORTIE' THEN quantite ELSE 0 END) as total_picked,
            ROUND(
                100.0 * SUM(CASE WHEN m.type = 'SORTIE' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0),
                1
            ) as outbound_percentage
        FROM warehouses w
        LEFT JOIN produits p ON w.id = p.warehouse_id
        LEFT JOIN mouvements m ON w.id = m.warehouse_id
            AND m.date_heure >= date('now', '-{lookback_days} days')
        GROUP BY w.id, w.name, w.location
        ORDER BY total_movements DESC
    """, conn)

    # 3. Top products across all warehouses
    report['top_products'] = pd.read_sql_query(f"""
        SELECT
            p.no_produit,
            p.nom_produit,
            COUNT(DISTINCT m.warehouse_id) as warehouse_count,
            SUM(CASE WHEN m.type = 'SORTIE' THEN 1 ELSE 0 END) as total_outbound,
            GROUP_CONCAT(DISTINCT m.warehouse_id) as warehouses
        FROM mouvements m
        JOIN produits p ON m.no_produit = p.no_produit AND m.warehouse_id = p.warehouse_id
        WHERE m.date_heure >= date('now', '-{lookback_days} days')
        GROUP BY p.no_produit, p.nom_produit
        ORDER BY total_outbound DESC
        LIMIT 20
    """, conn)

    return report

Output Format

Consolidated Excel Report

File: consolidated_report_20250121.xlsx

Sheet 1: Executive Summary

┌─────────────────────────────────────────────────────┐
│         CONSOLIDATED WAREHOUSE REPORT              │
│         Period: 2024-12-22 to 2025-01-21           │
│         Generated: 2025-01-21 14:30:45             │
├─────────────────────────────────────────────────────┤
│                                                     │
│  ENTERPRISE OVERVIEW                                │
│  ─────────────────                                  │
│  Total Warehouses: 3                               │
│  Total Products: 3,677                             │
│  Total Movements: 76,456                          │
│  Total Outbound: 48,234 (63.1%)                    │
│  Global Avg: 1,607 picks/day                       │
│                                                     │
│  WAREHOUSE BREAKDOWN                               │
│  ────────────────────────                          │
│  Paris:        28,901 movements (37.8%)            │
│  Lyon:         15,432 movements (20.2%)            │
│  Marseille:    32,123 movements (42.0%)            │
└─────────────────────────────────────────────────────┘

Sheet 2: Warehouse Comparison

Warehouse	Products	Movements	Outbound	% of Total	Picks/Day
Marseille	1,456	32,123	19,876	42.0%	1,071
Paris	1,234	28,901	18,234	37.8%	963
Lyon	987	15,432	10,124	20.2%	514
TOTAL	3,677	76,456	48,234	100%	1,607

Sheet 3: Top Products (Enterprise-wide)

Product	Name	Warehouses	Total Outbound	% Share
1001	Product Alpha	3	5,234	10.9%
1045	Product Beta	2	3,456	7.2%
1123	Product Gamma	3	2,987	6.2%

Implementation Plan

Phase 1: Consolidation Queries (1-2 days)

• Executive summary query
• Per-warehouse breakdown
• Cross-warehouse product ranking
• Aggregate metrics calculation

Phase 2: Report Generation (1 day)

• Multi-sheet Excel report
• Executive dashboard sheet
• Warehouse comparison sheet
• Cross-warehouse top products
• Charts and visualizations

CLI Usage

# Generate consolidated report
wareflow export --consolidated

# Custom lookback period
wareflow export --consolidated --days 60

# Custom output path
wareflow export --consolidated --output ./reports/

# Include executive summary only
wareflow export --consolidated --summary-only

# Include specific warehouses
wareflow export --consolidated --warehouses paris,lyon

Report Sections

1. Executive Dashboard

• KPIs across all warehouses
• Period-over-period comparison
• Key highlights and alerts
• Chart: Movement trends by warehouse

2. Warehouse Comparison

• Side-by-side metrics
• Benchmark vs average
• Performance ranking
• Chart: Relative performance

3. Product Analysis

• Top products (enterprise-wide)
• Products in multiple warehouses
• ABC distribution comparison
• Chart: Product overlap

4. Operational Metrics

• Staffing comparison
• Throughput comparison
• Efficiency comparison
• Chart: Efficiency metrics

5. Trends

• Daily movements (all warehouses)
• Growth rate by warehouse
• Seasonal patterns
• Chart: Consolidated trend line

Success Criteria

• Generate multi-sheet consolidated report
• Include executive dashboard
• Show per-warehouse breakdown
• Display cross-warehouse top products
• Add charts and visualizations
• Support warehouse filtering
• Complete report generation in < 15 seconds

Distribution Strategy

Email Reports

# Send consolidated report via email
wareflow export --consolidated --email execs@company.com

Scheduled Reports

# config/schedules.yaml
consolidated_weekly:
  frequency: "friday 08:00"
  recipients: ["execs@company.com", "warehouse-managers@company.com"]
  lookback_days: 7
  include_charts: true

Web Dashboard (Future)

• Real-time consolidated metrics
• Interactive warehouse comparison
• Drill-down to per-warehouse details

Future Enhancements

• Automated Distribution: Email reports on schedule
• Interactive Dashboard: Web-based real-time view
• Drill-Down: From consolidated to warehouse-specific
• Alerts: Cross-warehouse anomalies
• Forecasting: Consolidated demand forecasting

Dependencies

Required

• ARCH-001 (multi-warehouse support)
• CORE-004 (export command)
• At least 2 warehouses with data

Related Issues

• Depends on: ARCH-001, CORE-004
• Related to: ARCH-002 (Cross-warehouse Comparison)
• Enables: Executive reporting

References

• Multi-warehouse architecture: docs/FUTURE.md
• Export command: CORE-004

Notes

Consolidated reporting is critical for executive visibility:

• Single view of all operations
• Performance benchmarking
• Resource allocation decisions
• Strategic planning

Key benefits:

• Executive: High-level overview for decision-making
• Operations: Identify best practices and outliers
• Finance: Consolidated metrics for financial reporting
• Planning: Enterprise-wide resource optimization

The report should be:

• Actionable: Highlight key insights and recommendations
• Visual: Charts and graphs for quick understanding
• Timely: Generated on schedule (daily/weekly/monthly)
• Distributed: Automatically sent to stakeholders