<?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;
}
}
