ifnex/04_Laravel/app/Imports/HistoricalShipmentsImport.php

<?php

namespace App\Imports;

use App\Enums\ShipmentDirection;
use App\Enums\ShipmentStatus;
use App\Enums\ShipmentType;
use App\Models\Country;
use App\Models\Shipment;
use App\Models\ShipmentItem;
use Maatwebsite\Excel\Concerns\OnEachRow;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Row;

class HistoricalShipmentsImport implements WithMultipleSheets
{
    public function sheets(): array
    {
        return [
            'List' => new ShipmentsSheetImport(),
        ];
    }
}

class ShipmentsSheetImport implements OnEachRow, WithStartRow
{
    public function startRow(): int
    {
        return 3;
    }

    public function onRow(Row $row)
    {
        $cells = $row->toArray();

        if (empty($cells[0]) || $cells[0] === 'HAWB No.' || $cells[0] === 'Date') {
            return;
        }

        $awbNo = (string) $cells[0];
        if (str_starts_with($awbNo, '=')) {
            return;
        }

        $typeRaw = strtoupper(trim((string) ($cells[7] ?? '')));
        $serviceRaw = strtoupper(trim((string) ($cells[6] ?? '')));

        $type = match (true) {
            str_contains($typeRaw, 'DOC') && str_contains($typeRaw, 'ECO') => ShipmentType::DocEconomy,
            str_contains($typeRaw, 'DOC') => ShipmentType::DocNormal,
            default => ShipmentType::Parcel,
        };

        $direction = str_contains($serviceRaw, 'INBOUND') ? ShipmentDirection::Import : ShipmentDirection::Export;

        $fromCountry = $this->resolveCountry($cells[3] ?? '');
        $toCountry = $this->resolveCountry($cells[4] ?? '');

        $date = $cells[1] ?? null;
        if ($date && !str_contains((string) $date, '-')) {
            $date = null;
        }

        if (Shipment::where('awb_no', $awbNo)->exists()) {
            return;
        }

        $shipment = Shipment::create([
            'awb_no' => $awbNo,
            'direction' => $direction,
            'type' => $type,
            'status' => ShipmentStatus::Processed,
            'reason_for_export' => $cells[42] ?? null,
            'forwarder' => $cells[2] ?? null,
            'from_country_id' => $fromCountry?->id,
            'to_country_id' => $toCountry?->id,
            'weight' => $this->toFloat($cells[8] ?? null),
            'volumetric_weight' => $this->toFloat($cells[9] ?? null),
            'chargeable_weight' => $this->toFloat($cells[12] ?? null),
            'dimensions' => $this->buildDimensions($cells[13] ?? null, $cells[14] ?? null, $cells[15] ?? null),
            'sender_name' => $cells[18] ?? null,
            'sender_company' => $cells[17] ?? null,
            'sender_phone' => $cells[19] ?? null,
            'sender_email' => $cells[20] ?? null,
            'sender_address' => $cells[21] ?? null,
            'sender_city' => $cells[22] ?? null,
            'sender_zip' => $cells[23] ?? null,
            'sender_id_number' => $cells[24] ?? null,
            'receiver_name' => $cells[26] ?? null,
            'receiver_company' => $cells[25] ?? null,
            'receiver_phone' => $cells[27] ?? null,
            'receiver_email' => $cells[28] ?? null,
            'receiver_address' => $cells[29] ?? null,
            'receiver_city' => $cells[30] ?? null,
            'receiver_zip' => $cells[31] ?? null,
            'receiver_id_number' => $cells[32] ?? null,
            'shipping_price' => $this->toFloat($cells[33] ?? null),
            'extra_service' => $this->toFloat($cells[34] ?? null) ?? 0,
            'packing_cost' => $this->toFloat($cells[36] ?? null) ?? 0,
            'domestic_pickup' => $this->toFloat($cells[35] ?? null) ?? 0,
            'domestic_delivery' => $this->toFloat($cells[37] ?? null) ?? 0,
            'warehousing_cost' => $this->toFloat($cells[38] ?? null) ?? 0,
            'discount' => $this->toFloat($cells[39] ?? null) ?? 0,
            'total_fee' => $this->toFloat($cells[40] ?? null),
            'net_dirham' => null,
            'net_rial' => null,
            'created_at' => $date,
        ]);

        for ($i = 0; $i < 9; $i++) {
            $base = 43 + ($i * 6);

            $description = $cells[$base] ?? null;
            $hsCode = $cells[$base + 1] ?? null;
            $quantity = $this->toFloat($cells[$base + 2] ?? null);
            $unitPrice = $this->toFloat($cells[$base + 3] ?? null);
            $totalUsd = $this->toFloat($cells[$base + 4] ?? null);

            if (empty($description) && empty($hsCode)) {
                continue;
            }

            ShipmentItem::create([
                'shipment_id' => $shipment->id,
                'row_number' => $i + 1,
                'description' => $description,
                'hs_code' => $hsCode,
                'quantity' => $quantity ?? 0,
                'unit_price' => $unitPrice ?? 0,
                'total_usd' => $totalUsd ?? 0,
            ]);
        }
    }

    private function toFloat($value): ?float
    {
        if ($value === null || $value === '') {
            return null;
        }

        $value = str_replace([',', ' '], '', (string) $value);

        return is_numeric($value) ? (float) $value : null;
    }

    private function buildDimensions($w, $l, $h): ?string
    {
        $parts = array_filter([$w, $l, $h]);

        return $parts ? implode(' x ', $parts) . ' cm' : null;
    }

    private function resolveCountry(string $raw): ?Country
    {
        $raw = trim($raw);

        if (preg_match('/\(([A-Z]{2})\)/', $raw, $matches)) {
            return Country::where('iso_code', $matches[1])->first();
        }

        return Country::where('name', $raw)->first();
    }
}