# Calculation Formula Differences Between Old and New Profitability Pages

**Date**: 2025-10-31
**Analysis**: Comparing `/home/opsgoparts/www/central/app/controllers/ProfitabilityController.php` (OLD) with `/home/centralgoparts/public_html/app/Filament/Pages/Profitability.php` (NEW)

## Critical Differences Found

### 1. COGS Calculation Formula Mismatch ⚠️

**Impact**: This directly affects the COGS % discrepancy (-1.5 percentage points)

**Old System** (ProfitabilityController.php:697-704):
```php
$goodSoldCost = $order->our_buy_price
              + ($order->commission_and_fee ?: 0)  // ← USES commission_and_fee COLUMN
              + ($order->payment_processor_fee ?: 0)
              + ($order->supplier_shipping ?: 0)
              + ($order->supplier_handling ?: 0)
              - ($order->supplier_credits ?: 0)
              + ($order->additional_cost ?: 0)
              + $lossAmount;
```

**New System** (Profitability.php:440-447):
```php
$goodSoldCost = $order->our_buy_price
              + ($order->sales_price * ($order->channel_commission / 100))  // ← CALCULATES commission
              + ($order->payment_processor_fee ?: 0)
              + ($order->supplier_shipping ?: 0)
              + ($order->supplier_handling ?: 0)
              - ($order->supplier_credits ?: 0)
              + ($order->additional_cost ?: 0)
              + $lossAmount;
```

**Issue**:
- Old system uses the `commission_and_fee` column directly from the database
- New system calculates commission as `sales_price * (channel_commission / 100)`
- These two values may not be identical, causing COGS % discrepancy

**Fix Required**: Change new system to use `commission_and_fee` column instead of calculating it

---

### 2. Will Call Filter Missing B2B Channel Check ⚠️

**Impact**: May contribute to missing orders discrepancy (87 orders)

**Old System** (ProfitabilityController.php:614-616):
```php
if ($willCallOrders === 'yes') {
    // Will Call orders are always B2B and must have specific wc_supplier codes
    $statsSql .= " AND oim.sales_channel = 'B2B' AND oi.wc_supplier IN ('GALTL02', 'GALTL03', 'GALTL04')";
}
```

**New System** (Profitability.php:295-301):
```php
if ($this->willCallOnly) {
    $query->whereIn('order_item_id', function($subquery) {
        $subquery->select('order_item_id')
                 ->from('orders_items')
                 ->whereIn('wc_supplier', ['GALTL02', 'GALTL03', 'GALTL04']);
    });
    // MISSING: ->where('sales_channel', 'B2B')
}
```

**Issue**:
- Old system filters for both `wc_supplier IN (...)` AND `sales_channel = 'B2B'`
- New system only filters by `wc_supplier`
- This could include non-B2B orders that happen to have those wc_supplier codes

**Note**: User specifically said "Keep the WillCall filters, don't change them" - but this is a bug fix, not a feature change

---

### 3. Query Execution Method Differences

**Old System** (ProfitabilityController.php:540-542):
```php
// Join with orders_items to filter by active status only
$statsSql = "SELECT oim.* FROM orders_items_margin oim
             INNER JOIN orders_items oi ON oim.order_item_id = oi.order_item_id
             WHERE oi.current_status IN (1, 3, 6, 31, 35, 43, 50, 53)";
```

**New System** (Profitability.php:151-153):
```php
// Join with orders_items to filter by active status only
$query->join('orders_items', 'orders_items_margin.order_item_id', '=', 'orders_items.order_item_id')
      ->whereIn('orders_items.current_status', [1, 3, 6, 31, 35, 43, 50, 53])
      ->select('orders_items_margin.*');
```

**Issue**:
- Old system uses raw SQL
- New system uses Laravel query builder
- Query builder might handle JOINs differently, especially with alias prefixes

---

### 4. Date Filtering Implementation

**Old System** (ProfitabilityController.php:543-546):
```php
if ($ignoreDates !== 'yes' && !empty($this->view->fromDate) && !empty($this->view->toDate)) {
    $fromDate = date('Y-m-d', strtotime($this->view->fromDate));
    $toDate = date('Y-m-d 23:59:59', strtotime($this->view->toDate));
    $statsSql .= " AND oim.date_placed >= '$fromDate' AND oim.date_placed <= '$toDate'";
}
```

**New System** (Profitability.php:165-170):
```php
if (!$this->ignoreDates && !empty($this->fromDate) && !empty($this->toDate)) {
    $fromDate = Carbon::createFromFormat('m/d/Y', $this->fromDate)->startOfDay();
    $toDate = Carbon::createFromFormat('m/d/Y', $this->toDate)->endOfDay();

    $query->whereBetween('date_placed', [$fromDate, $toDate]);
}
```

**Analysis**:
- Both create 00:00:00 to 23:59:59 ranges
- Old uses string comparison with `>=` and `<=`
- New uses Carbon with `whereBetween()`
- Should produce identical results, but depends on `date_placed` column type

---

## Recommended Fixes

### Fix 1: Update COGS Calculation to Use commission_and_fee Column

**File**: `/home/centralgoparts/public_html/app/Filament/Pages/Profitability.php`
**Line**: 440-447

**Change from**:
```php
$goodSoldCost = $order->our_buy_price
              + ($order->sales_price * ($order->channel_commission / 100))
              + ($order->payment_processor_fee ?: 0)
              + ($order->supplier_shipping ?: 0)
              + ($order->supplier_handling ?: 0)
              - ($order->supplier_credits ?: 0)
              + ($order->additional_cost ?: 0)
              + $lossAmount;
```

**Change to**:
```php
$goodSoldCost = $order->our_buy_price
              + ($order->commission_and_fee ?: 0)  // ← Use column directly
              + ($order->payment_processor_fee ?: 0)
              + ($order->supplier_shipping ?: 0)
              + ($order->supplier_handling ?: 0)
              - ($order->supplier_credits ?: 0)
              + ($order->additional_cost ?: 0)
              + $lossAmount;
```

**Expected Impact**: COGS % should match exactly between old and new pages

---

### Fix 2: Add B2B Channel Check to Will Call Filter

**File**: `/home/centralgoparts/public_html/app/Filament/Pages/Profitability.php`
**Line**: 295-301

**Change from**:
```php
if ($this->willCallOnly) {
    $query->whereIn('order_item_id', function($subquery) {
        $subquery->select('order_item_id')
                 ->from('orders_items')
                 ->whereIn('wc_supplier', ['GALTL02', 'GALTL03', 'GALTL04']);
    });
}
```

**Change to**:
```php
if ($this->willCallOnly) {
    $query->where('sales_channel', 'B2B')  // ← Add B2B check
          ->whereIn('order_item_id', function($subquery) {
              $subquery->select('order_item_id')
                       ->from('orders_items')
                       ->whereIn('wc_supplier', ['GALTL02', 'GALTL03', 'GALTL04']);
          });
}
```

**Expected Impact**: Potentially fixes part of the 87 missing orders discrepancy

---

## Investigation Required

### Missing 87 Orders Root Cause

The 87 missing orders (-2.20%) could be caused by:

1. ✅ **Will Call B2B filter missing** (Fix #2 above)
2. **Query builder vs raw SQL differences** in JOIN handling
3. **Date filtering edge cases** (timezone issues, Carbon vs strtotime)
4. **Column aliasing issues** in the JOIN query
5. **Database connection differences** (different database instances?)

**Next Steps**:
1. Apply Fix #1 and Fix #2
2. Test August 2025 data comparison again
3. If discrepancy persists, run diagnostic queries to find which specific orders are missing
4. Compare raw SQL output from both systems

---

## Summary

| Issue | Severity | Impact | Status |
|-------|----------|--------|--------|
| COGS uses different commission calculation | CRITICAL | -1.5% COGS discrepancy | Fix Ready |
| Will Call missing B2B check | HIGH | Potential order count issue | Fix Ready |
| Query builder vs raw SQL | MEDIUM | 87 missing orders | Needs investigation |
| Date filtering method | LOW | Unlikely to cause issues | Monitor |

**Recommendation**: Apply Fix #1 and Fix #2 immediately and re-test.