hqland-app/app/Console/Commands/ExportDebtReport.php

<?php

namespace App\Console\Commands;

use App\Models\Contract;
use App\Models\PaymentScheduleItem;
use Illuminate\Console\Command;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportDebtReport extends Command
{
    protected $signature = 'export:debt-report
                            {--output=storage/app/reports/bao-cao-cong-no.xlsx : Đường dẫn file xuất}
                            {--project= : Lọc theo UUID dự án}';

    protected $description = 'Xuất báo cáo công nợ khách hàng ra file Excel';

    public function handle(): int
    {
        $outputPath = $this->option('output');
        $projectId = $this->option('project');

        // Đảm bảo thư mục tồn tại
        $dir = dirname($outputPath);
        if (! is_dir($dir)) {
            mkdir($dir, 0755, true);
        }

        $this->info('Đang tải dữ liệu...');

        // ===== Sheet 1: Tổng hợp công nợ =====
        $contractsQuery = Contract::query()
            ->with(['customers', 'product.project', 'paymentSchedule.items'])
            ->when($projectId, fn ($q) => $q->whereHas('product', fn ($q2) => $q2->where('project_id', $projectId)))
            ->orderBy('contract_number');

        $contracts = $contractsQuery->get();

        $spreadsheet = new Spreadsheet();
        $sheet1 = $spreadsheet->getActiveSheet();
        $sheet1->setTitle('Tổng hợp công nợ');

        // Header
        $headers1 = ['STT', 'Số HĐMB', 'Khách hàng', 'Dự án', 'Lô đất', 'Giá trị HĐ (VNĐ)', 'Đã thu (VNĐ)', 'Còn lại (VNĐ)', 'Trạng thái', 'Ngày ký'];
        $this->writeHeader($sheet1, $headers1);

        $row = 2;
        foreach ($contracts as $index => $contract) {
            $sheet1->setCellValue('A' . $row, $index + 1);
            $sheet1->setCellValue('B' . $row, $contract->contract_number);
            $sheet1->setCellValue('C' . $row, $contract->customers->pluck('full_name')->implode(', '));
            $sheet1->setCellValue('D' . $row, $contract->product->project->name ?? '');
            $sheet1->setCellValue('E' . $row, $contract->product->code ?? '');
            $sheet1->setCellValue('F' . $row, (float) $contract->total_value);
            $sheet1->setCellValue('G' . $row, (float) $contract->paid_amount);
            $sheet1->setCellValue('H' . $row, (float) $contract->remaining_amount);
            $sheet1->setCellValue('I' . $row, $contract->status);
            $sheet1->setCellValue('J' . $row, $contract->signing_date ? $contract->signing_date->format('d/m/Y') : '');
            $row++;
        }

        $this->formatNumberColumns($sheet1, ['F', 'G', 'H'], $row - 1);
        $this->autoSizeColumns($sheet1, $headers1);

        // ===== Sheet 2: Chi tiết đợt thanh toán chưa đủ =====
        $sheet2 = $spreadsheet->createSheet();
        $sheet2->setTitle('Chi tiết đợt TT');

        $headers2 = ['STT', 'Số HĐMB', 'Khách hàng', 'Đợt', 'Loại', 'Ngày đến hạn', 'Số tiền đợt (VNĐ)', 'Đã thu (VNĐ)', 'Còn thiếu (VNĐ)'];
        $this->writeHeader($sheet2, $headers2);

        $itemsQuery = PaymentScheduleItem::query()
            ->with(['schedule.contract.customers', 'payments'])
            ->whereHas('schedule.contract')
            ->when($projectId, fn ($q) => $q->whereHas('schedule.contract.product', fn ($q2) => $q2->where('project_id', $projectId)))
            ->orderBy('due_date');

        $items = $itemsQuery->get();

        $row = 2;
        $stt = 1;
        foreach ($items as $item) {
            $contract = $item->schedule?->contract;
            if (! $contract) continue;

            $paid = (float) $item->paid_amount;
            $remaining = (float) $item->remaining_amount;

            $sheet2->setCellValue('A' . $row, $stt);
            $sheet2->setCellValue('B' . $row, $contract->contract_number);
            $sheet2->setCellValue('C' . $row, $contract->customers->pluck('full_name')->implode(', '));
            $sheet2->setCellValue('D' . $row, $item->installment_no);
            $sheet2->setCellValue('E' . $row, $item->type?->getLabel() ?? (string) $item->type);
            $sheet2->setCellValue('F' . $row, $item->due_date ? $item->due_date->format('d/m/Y') : '');
            $sheet2->setCellValue('G' . $row, (float) $item->amount);
            $sheet2->setCellValue('H' . $row, $paid);
            $sheet2->setCellValue('I' . $row, $remaining);
            $row++;
            $stt++;
        }

        $this->formatNumberColumns($sheet2, ['G', 'H', 'I'], $row - 1);
        $this->autoSizeColumns($sheet2, $headers2);

        // Lưu file
        $writer = new Xlsx($spreadsheet);
        $writer->save($outputPath);

        $this->info("Xuất báo cáo thành công: {$outputPath}");
        $this->info("- Tổng hợp: {$contracts->count()} hợp đồng");
        $this->info("- Chi tiết đợt: {$items->count()} dòng");

        return self::SUCCESS;
    }

    private function writeHeader($sheet, array $headers): void
    {
        foreach ($headers as $colIndex => $header) {
            $coord = [$colIndex + 1, 1];
            $sheet->setCellValue($coord, $header);
            $cell = $sheet->getCell($coord);
            $cell->getStyle()->getFont()->setBold(true);
            $cell->getStyle()->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('E5E7EB');
            $cell->getStyle()->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $cell->getStyle()->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        }
    }

    private function formatNumberColumns($sheet, array $columns, int $lastRow): void
    {
        foreach ($columns as $col) {
            $sheet->getStyle("{$col}2:{$col}{$lastRow}")
                ->getNumberFormat()
                ->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        }
    }

    private function autoSizeColumns($sheet, array $headers): void
    {
        foreach (range(1, count($headers)) as $colIndex) {
            $colLetter = Coordinate::stringFromColumnIndex($colIndex);
            $sheet->getColumnDimension($colLetter)->setAutoSize(true);
        }
    }
}