Kent-logistics-Laravel/app/Exports/OrdersExport.php

<?php

namespace App\Exports;

use App\Models\Order;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Carbon\Carbon;

class OrdersExport implements FromCollection, WithHeadings
{
    protected Request $request;

    public function __construct(Request $request)
    {
        $this->request = $request;
    }

    private function buildQuery()
    {
        $query = Order::query()->with([
            'markList',
            'invoice',
            'shipments',
        ]);

        // SEARCH
        if ($this->request->filled('search')) {
            $search = trim($this->request->search);

            $query->where(function ($q) use ($search) {
                $q->where('orders.order_id', 'like', "%{$search}%")
                  ->orWhereHas('markList', function ($q2) use ($search) {
                      $q2->where('company_name', 'like', "%{$search}%")
                         ->orWhere('customer_id', 'like', "%{$search}%")
                         ->orWhere('origin', 'like', "%{$search}%")
                         ->orWhere('destination', 'like', "%{$search}%");
                  })
                  ->orWhereHas('invoice', function ($q3) use ($search) {
                      $q3->where('invoice_number', 'like', "%{$search}%");
                  })
                  ->orWhereHas('shipments', function ($q4) use ($search) {
                      // ✅ FIXED
                      $q4->where('shipments.shipment_id', 'like', "%{$search}%");
                  });
            });
        }

        // INVOICE STATUS
        // INVOICE STATUS (FIXED)
        if ($this->request->filled('status')) {
            $query->where(function ($q) {
                $q->whereHas('invoice', function ($q2) {
                    $q2->where('status', $this->request->status);
                })
                ->orWhereDoesntHave('invoice');
            });
        }


        // SHIPMENT STATUS (FIXED)
        if ($this->request->filled('shipment')) {
            $query->where(function ($q) {
                $q->whereHas('shipments', function ($q2) {
                    $q2->where('status', $this->request->shipment);
                })
                ->orWhereDoesntHave('shipments');
            });
        }


        // DATE RANGE
        if ($this->request->filled('from_date')) {
            $query->whereDate('orders.created_at', '>=', $this->request->from_date);
        }

        if ($this->request->filled('to_date')) {
            $query->whereDate('orders.created_at', '<=', $this->request->to_date);
        }

        return $query->latest('orders.id');
    }

    public function collection()
    {
        return $this->buildQuery()->get()->map(function ($order) {

            $mark     = $order->markList;
            $invoice  = $order->invoice;
            $shipment = $order->shipments->first();

            return [
                'Order ID'        => $order->order_id ?? '-',
                'Shipment ID'     => $shipment?->shipment_id ?? '-',
                'Customer ID'     => $mark?->customer_id ?? '-',
                'Company'         => $mark?->company_name ?? '-',
                'Origin'          => $mark?->origin ?? $order->origin ?? '-',
                'Destination'     => $mark?->destination ?? $order->destination ?? '-',
                'Order Date'      => $order->created_at
                                      ? $order->created_at->format('d-m-Y')
                                      : '-',
                'Invoice No'      => $invoice?->invoice_number ?? '-',
                'Invoice Date'    => $invoice?->invoice_date
                                      ? Carbon::parse($invoice->invoice_date)->format('d-m-Y')
                                      : '-',
                'Amount'          => $invoice?->final_amount !== null
                                      ? number_format($invoice->final_amount, 2)
                                      : '0.00',
                'Amount + GST'    => $invoice?->final_amount_with_gst !== null
                                      ? number_format($invoice->final_amount_with_gst, 2)
                                      : '0.00',
                'Invoice Status'  => ucfirst($invoice?->status ?? 'pending'),
                'Shipment Status' => ucfirst(str_replace('_', ' ', $shipment?->status ?? 'pending')),
            ];
        });
    }

    public function headings(): array
    {
        return [
            'Order ID',
            'Shipment ID',
            'Customer ID',
            'Company',
            'Origin',
            'Destination',
            'Order Date',
            'Invoice No',
            'Invoice Date',
            'Amount',
            'Amount + GST',
            'Invoice Status',
            'Shipment Status',
        ];
    }
}