XLSX Import for Header Detail Data - Advanced

composer require jenssegers/laravel-mongodb

<?php
namespace App\Models;

// Use the MongoDB Eloquent Model
use Jenssegers\Mongodb\Eloquent\Model;

class JournalHeader extends Model
{
    protected $connection = 'mongodb'; // Specify the connection
    protected $collection = 'journal_headers'; // Specify the collection name

    // You can still use fillable, but MongoDB is schema-less
    protected $fillable = [
        'journalId', 'journalDescription', 'journalDate', 'totalAmount', 'details' // Make the embedded array fillable
    ];
}

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;

class ImportHeaderDetail extends Command
{
    /**
     * The name and signature of the console command.
     */
    protected $signature = 'import:header-detail
                            {file : The path to the Excel file to import.}
                            {--entities= : Map types to Models (e.g., header:JournalHeader,detail:JournalDetail)}
                            {--key= : The primary/foreign key column name (e.g., journalId). Required.}
                            {--embed : For NoSQL, embed child entities as an array in the parent document.}
                            {--sheet-title-as= : Use each sheet\'s title as a value for the specified field.}';

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

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $options = $this->getValidatedOptions();
        if (!$options) return 1;

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

        DB::beginTransaction();
        try {
            if ($options['sheetTitleAs']) {
                $this->processMultiSheetFile($options);
            } else {
                $this->processSingleSheetFile($options);
            }

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

        } catch (\Exception $e) {
            DB::rollBack();
            $this->error("An error occurred: " . $e->getMessage());
            $this->error("Import failed. All changes have been rolled back.");
            return 1;
        }
    }

    /**
     * Processes a file with multiple sheets, where each sheet is a self-contained record.
     */
    private function processMultiSheetFile(array $options): void
    {
        $this->info("Multi-sheet mode enabled. Using sheet titles for '{$options['sheetTitleAs']}' field.");
        $sheetNames = (new \Maatwebsite\Excel\Excel)->getSheetNames($options['filePath']);

        foreach ($sheetNames as $sheetName) {
            $this->line("--- Processing Sheet: {$sheetName} ---");
            $rows = Excel::toArray(new \stdClass(), $options['filePath'], null, \Maatwebsite\Excel\Excel::XLSX, ['onlySheets' => [$sheetName]])[0];

            // Inject the sheet title as a key-value pair for every row in this sheet
            $injectedData = [$options['sheetTitleAs'] => $sheetName];

            $this->processRows($rows, $options, $injectedData);
        }
    }

    /**
     * Processes a standard single-sheet file.
     */
    private function processSingleSheetFile(array $options): void
    {
        $this->info("Single-sheet mode enabled.");
        $rows = Excel::toArray(new \stdClass(), $options['filePath'])[0];
        $this->processRows($rows, $options);
    }

    /**
     * The core logic for processing a set of rows (from a single sheet).
     */
    private function processRows(array $rows, array $options, array $injectedData = []): void
    {
        $currentEntityType = null;
        $currentHeaders = [];
        $lastParentKeyValue = null;

        // Variables for embed mode
        $currentParentInstance = null;
        $collectedDetails = [];

        foreach ($rows as $rowIndex => $row) {
            if (empty(array_filter($row))) continue;

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

            if (array_key_exists($entityType, $options['entityMappings'])) {
                // When we hit a new block type, process the previous parent if in embed mode
                if ($options['embed'] && $currentParentInstance && $entityType === 'header') {
                    $this->saveParentWithEmbeds($currentParentInstance, $collectedDetails, $options['keyField']);
                    $currentParentInstance = null; // Reset for the new parent
                }

                $currentEntityType = $entityType;
                $currentHeaders = $rows[$rowIndex + 1];
                $this->line("Processing new block: '{$currentEntityType}'");
                continue;
            }

            if ($currentEntityType && $row === $currentHeaders) continue;

            if ($currentEntityType) {
                $filteredHeaders = array_filter($currentHeaders, fn($h) => $h !== null);
                $dataRow = array_slice($row, 0, count($filteredHeaders));
                $data = array_combine($filteredHeaders, $dataRow) + $injectedData;

                $modelClass = $options['entityMappings'][$currentEntityType];

                if ($entityType === 'header' || $entityType === 'main') {
                    $lastParentKeyValue = $data[$options['keyField']];
                    if ($options['embed']) {
                        $currentParentInstance = new $modelClass($data);
                        $collectedDetails = []; // Reset detail collector
                    } else {
                        $modelClass::create($data);
                    }
                    $this->info("  Saved Header with key: {$lastParentKeyValue}");
                } else { // This is a detail entity
                    if ($options['embed']) {
                        // In embed mode, just collect the data
                        if (!$currentParentInstance) throw new \Exception("Found detail '{$entityType}' without a parent on row " . ($rowIndex+1));
                        $collectedDetails[$currentEntityType][] = $data;
                        $this->line("    - Collected '{$entityType}' detail for embedding.");
                    } else {
                        // In relational mode, save it directly
                        if (!isset($data[$options['keyField']]) || empty($data[$options['keyField']])) {
                            $data[$options['keyField']] = $lastParentKeyValue;
                            if (!$lastParentKeyValue) throw new \Exception("Found detail '{$entityType}' without an implicit or explicit key on row " . ($rowIndex+1));
                        }
                        $modelClass::create($data);
                        $this->line("    - Saved '{$entityType}' detail for key {$data[$options['keyField']]}");
                    }
                }
            }
        }

        // After the loop, save the very last parent record if in embed mode
        if ($options['embed'] && $currentParentInstance) {
            $this->saveParentWithEmbeds($currentParentInstance, $collectedDetails, $options['keyField']);
        }
    }

    /**
     * Helper for embed mode: attaches collected details to the parent and saves it.
     */
    private function saveParentWithEmbeds(Model $parent, array $details, string $keyField): void
    {
        foreach ($details as $entityName => $dataArray) {
            $parent->{$entityName} = $dataArray;
        }
        $parent->save();
        $this->info("  Saved embedded document with key: {$parent->{$keyField}}");
    }

    /**
     * Validates and prepares command options for use.
     */
    private function getValidatedOptions(): ?array
    {
        $options = [
            'filePath' => $this->argument('file'),
            'keyField' => $this->option('key'),
            'embed' => $this->option('embed'),
            'sheetTitleAs' => $this->option('sheet-title-as'),
            'entityMappings' => $this->parseEntityMappings($this->option('entities')),
        ];

        if (!$options['keyField']) { $this->error('The --key option is required.'); return null; }
        if (empty($options['entityMappings'])) { $this->error('The --entities option is required.'); return null; }
        if (!file_exists($options['filePath'])) { $this->error("File not found at path: {$options['filePath']}"); return null; }
        if ($options['sheetTitleAs'] && pathinfo($options['filePath'], PATHINFO_EXTENSION) === 'csv') {
            $this->error('The --sheet-title-as option is not applicable to CSV files.'); return null;
        }

        return $options;
    }

    /**
     * Parses the --entities option string into a usable array.
     */
    private function parseEntityMappings(?string $entities): array
    {
        // Unchanged from previous version
        if (!$entities) return [];
        $mappings = [];
        $pairs = explode(',', $entities);
        foreach ($pairs as $pair) {
            list($key, $modelName) = explode(':', $pair);
            $mappings[strtolower(trim($key))] = 'App\\Models\\' . trim($modelName);
        }
        if (isset($mappings['header'])) {
            $mappings['main'] = $mappings['header'];
        }
        return $mappings;
    }
}

php artisan import:header-detail <file> --entities=<map> --key=<key> [--embed] [--sheet-title-as=<field>]

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

{
  "_id": "...",
  "employeeId": "E001",
  "name": "John Doe",
  "dateOfBirth": "1990-05-15",
  "dependent": [
    { "name": "Jane Doe", "relationship": "Spouse" },
    { "name": "Jimmy Doe", "relationship": "Child" }
  ],
  "education": [
    { "institution": "State University", "degree": "B.Sc. CompSci", "yearCompleted": 2012 }
  ]
}

php artisan import:header-detail storage/app/journals_by_id.xlsx \
--entities=header:JournalHeader,detail:JournalDetail \
--key=journalId \
--sheet-title-as=journalId

php artisan import:header-detail <file> --entities=<map> --key=<key> [--embed] [--sheet-title-as=<field>]

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

{
  "employeeId": "E001",
  "name": "John Doe",
  "dependent": [
    { "name": "Jane Doe", "relationship": "Spouse" }
  ],
  "education": [
    { "institution": "State University", "degree": "B.Sc. CompSci" }
  ]
}

php artisan import:header-detail storage/app/jurnal_per_id.xlsx \
--entities=header:JournalHeader,detail:JournalDetail \
--key=journalId \
--sheet-title-as=journalId