<?php
namespace App\Console\Commands;
use Illuminate\Console\Command;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\File;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
class ExportHeaderDetail extends Command
{
/**
* The name and signature of the console command.
*/
protected $signature = 'export:header-detail
{file : The output file path (e.g., storage/app/export.xlsx).}
{--template= : The path to the Excel template file to use for formatting.}
{--entities= : Map types to Models/relations (e.g., header:JournalHeader,detail:details).}
{--key= : The primary key field of the parent model.}
{--ids= : Optional comma-separated list of parent record keys to export.}
{--query= : Eloquent query conditions to filter parent records (e.g., "status:active").}
{--embed : Export from a NoSQL model with embedded arrays.}
{--sheet-title-as= : Group each parent record onto its own sheet, named by this field.}';
/**
* The console command description.
*/
protected $description = 'Exports database records into a structured Excel file, optionally using a template.';
public function handle()
{
$options = $this->getValidatedOptions();
if (!$options) return 1;
$parentModelClass = $options['entityMappings']['header'];
$query = $parentModelClass::query();
if ($options['ids']) {
$query->whereIn($options['keyField'], explode(',', $options['ids']));
}
if ($options['query']) {
try {
$this->applyQueryFilters($query, $options['query']);
} catch (\Exception $e) {
$this->error("Invalid query format: " . $e->getMessage()); return 1;
}
}
$parents = $query->get();
if ($parents->isEmpty()) {
$this->warn('No records found matching the criteria. No file was created.'); return 0;
}
$this->info("Found {$parents->count()} parent records to export. Generating file...");
File::ensureDirectoryExists(dirname($options['filePath']));
$this->handleTemplateExport($parents, $options);
$this->info("Export completed successfully! File saved to: {$options['filePath']}");
return 0;
}
private function handleTemplateExport($parents, array $options): void
{
$spreadsheet = IOFactory::load($options['templatePath']);
$templateSheet = $spreadsheet->getActiveSheet();
$isMultiSheet = count($parents) > 0 && $options['sheetTitleAs'];
if ($isMultiSheet) {
$spreadsheet->removeSheetByIndex(0);
}
foreach ($parents as $index => $parent) {
$sheet = $isMultiSheet ? $templateSheet->copy() : $templateSheet;
$sheetName = $options['sheetTitleAs'] ? (string) $parent->{$options['sheetTitleAs']} : "Export";
$sheet->setTitle($sheetName);
$this->populateSheetWithData($sheet, $parent, $options);
if ($isMultiSheet) {
$spreadsheet->addSheet($sheet);
}
}
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($options['filePath']);
}
private function populateSheetWithData(Worksheet $sheet, $parent, array $options): void
{
$placeholders = [];
for ($row = 1; $row <= $sheet->getHighestRow(); $row++) {
$entityType = $sheet->getCell("A{$row}")->getValue();
if (isset($options['entityMappings'][$entityType])) {
$placeholders[$entityType] = [
'rowIndex' => $row,
'fields' => $this->getFieldsFromRow($sheet, $row),
];
}
}
$reversedPlaceholders = array_reverse($placeholders, true);
foreach ($reversedPlaceholders as $entityType => $placeholder) {
$dataToInsert = [];
$isHeader = ($entityType === 'header' || $entityType === 'main');
if ($isHeader) {
$dataToInsert[] = $parent->toArray();
} else {
$relationName = $options['entityMappings'][$entityType];
$children = $options['embed'] ? ($parent->{$relationName} ?? []) : $parent->{$relationName};
if (!empty($children)) {
foreach ($children as $child) {
$dataToInsert[] = is_array($child) ? $child : $child->toArray();
}
}
}
if (!empty($dataToInsert)) {
$startRow = $placeholder['rowIndex'] + 1;
$numRows = count($dataToInsert);
$sheet->insertNewRowBefore($startRow, $numRows - 1); // Insert n-1 rows because the placeholder row will be replaced by the first data row
foreach ($dataToInsert as $i => $dataRow) {
$currentRow = $placeholder['rowIndex'] + $i;
$colIndex = 1; // Column A
foreach ($placeholder['fields'] as $field) {
// We start writing from column B (index 2)
$sheet->setCellValueByColumnAndRow($colIndex + 1, $currentRow, $dataRow[$field] ?? null);
$colIndex++;
}
}
} else {
// If no data, just remove the placeholder row
$sheet->removeRow($placeholder['rowIndex']);
}
}
}
private function getFieldsFromRow(Worksheet $sheet, int $rowIndex): array
{
$fields = [];
for ($col = 2; $col <= $sheet->getHighestColumnIndex(); $col++) {
$field = $sheet->getCellByColumnAndRow($col, $rowIndex)->getValue();
if (!empty($field)) {
$fields[] = $field;
} else {
break;
}
}
return $fields;
}
private function getValidatedOptions(): ?array
{
$templatePath = $this->option('template');
if (!$templatePath || !File::exists($templatePath)) {
$this->error('A valid --template path is required for this command.'); return null;
}
$entities = $this->option('entities');
$keyField = $this->option('key');
if (!$keyField || !$entities) {
$this->error('The --key and --entities options are required.'); return null;
}
return [
'filePath' => $this->argument('file'),
'templatePath' => $templatePath,
'keyField' => $keyField,
'ids' => $this->option('ids'),
'query' => $this->option('query'),
'embed' => $this->option('embed'),
'sheetTitleAs' => $this->option('sheet-title-as'),
'entityMappings' => $this->parseEntityMappings($entities),
];
}
private function parseEntityMappings(string $entities): array
{
$mappings = [];
$pairs = explode(',', $entities);
foreach ($pairs as $pair) {
list($key, $modelOrRelation) = explode(':', $pair);
$key = strtolower(trim($key));
$modelOrRelation = trim($modelOrRelation);
if ($key === 'header' || $key === 'main') {
$mappings[$key] = 'App\\Models\\' . $modelOrRelation;
} else {
$mappings[$key] = $modelOrRelation;
}
}
if (isset($mappings['header'])) $mappings['main'] = $mappings['header'];
return $mappings;
}
private function applyQueryFilters(Builder $query, string $queryString): void
{
$conditions = explode(';', $queryString);
foreach ($conditions as $condition) {
if (empty(trim($condition))) continue;
$parts = explode(':', $condition, 3);
if (count($parts) === 2) {
list($field, $value) = $parts;
$operator = '=';
} elseif (count($parts) === 3) {
list($field, $operator, $value) = $parts;
} else {
throw new \Exception("Malformed condition '{$condition}'. Use 'field:value' or 'field:operator:value'.");
}
$field = trim($field);
$operator = trim($operator);
if (strtolower($operator) === 'in') {
$query->whereIn($field, explode(',', $value));
} elseif (strtolower($operator) === 'notin') {
$query->whereNotIn($field, explode(',', $value));
} else {
$query->where($field, $operator, $value);
}
}
}
}
