XLSX Import for Header Detail Data

Schema::create('journal_headers', function (Blueprint $table) {
    $table->id();
    $table->string('journalId')->unique();
    $table->string('journalDescription');
    $table->date('journalDate');
    $table->decimal('totalAmount', 15, 2);
    $table->timestamps();
});

Schema::create('journal_details', function (Blueprint $table) {
    $table->id();
    $table->string('journalId'); // This is the foreign key
    $table->string('trxDescription');
    $table->date('trxDate');
    $table->integer('trxQty');
    // ... other detail columns
    $table->timestamps();

    $table->foreign('journalId')->references('journalId')->on('journal_headers')->onDelete('cascade');
});

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;

class JournalHeader extends Model
{
    protected $fillable = [
        'journalId', 'journalDescription', 'journalDate', 'totalAmount'
    ];
}

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;

class JournalDetail extends Model
{
    protected $fillable = [
        'journalId', 'trxDescription', 'trxDate', 'trxQty', //... etc.
    ];
}

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;

class ImportHeaderDetail extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'import:header-detail
                            {file : The path to the Excel file to import.}
                            {--entities= : Map entity types to Models (e.g., header:ModelName,detail:DetailModel)}
                            {--key= : The name of the primary/foreign key column (e.g., journalId). This is required.}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Parse a structured header/detail Excel file and save data to the database.';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $filePath = $this->argument('file');
        $keyField = $this->option('key');
        $entityMappings = $this->parseEntityMappings($this->option('entities'));

        // Validation for required options
        if (!$keyField) {
            $this->error('The --key option is required.');
            return 1;
        }
        if (empty($entityMappings)) {
            $this->error('Entity mappings are required. Use --entities=header:Model,child:Model,...');
            return 1;
        }
        if (!file_exists($filePath)) {
            $this->error("File not found at path: {$filePath}");
            return 1;
        }

        $this->info("Starting import of {$filePath}...");

        $rows = Excel::toArray(new \stdClass(), $filePath)[0];

        $currentEntityType = null;
        $currentHeaders = [];
        $lastParentKeyValue = null;

        DB::beginTransaction();
        try {
            foreach ($rows as $rowIndex => $row) {
                // Skip completely empty rows
                if (empty(array_filter($row))) {
                    continue;
                }

                $entityType = strtolower(trim($row[0] ?? ''));

                // This row defines a new entity type block (e.g., 'header', 'detail', 'education')
                if (array_key_exists($entityType, $entityMappings)) {
                    $currentEntityType = $entityType;
                    // The next row contains the column headers for this block
                    $currentHeaders = $rows[$rowIndex + 1];
                    $this->line("Processing new block: '{$currentEntityType}'");
                    continue;
                }

                // Skip the header row itself, as we've already stored it
                if ($currentEntityType && $row === $currentHeaders) {
                    continue;
                }

                // If we are inside a block, process this row as data
                if ($currentEntityType) {
                    $filteredHeaders = array_filter($currentHeaders, fn($h) => $h !== null);
                    $dataRow = array_slice($row, 0, count($filteredHeaders));
                    $data = array_combine($filteredHeaders, $dataRow);

                    $modelClass = $entityMappings[$currentEntityType];
                    $modelInstance = new $modelClass();

                    // Logic for linking child entities to the parent
                    if ($currentEntityType !== 'header' && $currentEntityType !== 'main') {
                        // If the key field is NOT explicitly provided in the data row,
                        // then use the key from the last header as a fallback.
                        if (!isset($data[$keyField]) || empty($data[$keyField])) {
                            if (!$lastParentKeyValue) {
                                throw new \Exception("Found a '{$currentEntityType}' row without a preceding 'header'/'main' row and no explicit key.");
                            }
                            // Assign the foreign key from the last saved parent
                            $data[$keyField] = $lastParentKeyValue;
                        }
                        // If the key IS provided in the data row, we will use that explicit value.
                    }

                    $modelInstance->fill($data);
                    $modelInstance->save();

                    // If this was a parent entity, store its key for subsequent children
                    if ($currentEntityType === 'header' || $currentEntityType === 'main') {
                        $lastParentKeyValue = $modelInstance->{$keyField};
                        $this->info("  Saved Header with key: {$lastParentKeyValue}");
                    } else {
                        $savedKey = $modelInstance->{$keyField};
                        $this->line("    - Saved '{$currentEntityType}' detail for key {$savedKey}");
                    }
                }
            }

            DB::commit();
            $this->info('Import completed successfully!');
            return 0;

        } catch (\Exception $e) {
            DB::rollBack();
            $this->error("An error occurred: " . $e->getMessage());
            $this->error("On row " . ($rowIndex + 1) . " while processing block '{$currentEntityType}'");
            return 1;
        }
    }

    /**
     * Parses the --entities option string into a usable array.
     */
    private function parseEntityMappings(?string $entities): array
    {
        if (!$entities) return [];
        $mappings = [];
        $pairs = explode(',', $entities);
        foreach ($pairs as $pair) {
            list($key, $modelName) = explode(':', $pair);
            // We assume models are in the App\Models namespace
            $mappings[strtolower(trim($key))] = 'App\\Models\\' . trim($modelName);
        }
        // Also map 'main' to whatever 'header' is, for convenience
        if (isset($mappings['header'])) {
            $mappings['main'] = $mappings['header'];
        }
        return $mappings;
    }
}

php artisan import:header-detail <file> --entities=<mappings> --key=<key_name>

php artisan import:header-detail storage/app/hr_data.xlsx --entities=header:PersonnelInfo,dependent:Dependent,education:EducationHistory --key=employeeId

php artisan import:header-detail storage/app/journals.xlsx --entities=header:JournalHeader,detail:JournalDetail --key=journalId

php artisan import:header-detail <file> --entities=<mappings> --key=<key_name>

php artisan import:header-detail storage/app/hr_data.xlsx --entities=header:PersonnelInfo,dependent:Dependent,education:EducationHistory --key=employeeId

php artisan import:header-detail storage/app/jurnal.xlsx --entities=header:JournalHeader,detail:JournalDetail --key=journalId