portal-api/app/Console/Commands/Whmcs/ExportMappingCommand.php

<?php

namespace App\Console\Commands\Whmcs;

use App\Integrations\Whmcs\Support\CustomerIdResolver;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportMappingCommand extends Command
{
    protected $signature = 'whmcs:export-mapping {--path= : Output .xlsx path (default storage/app/whmcs-mapping-<time>.xlsx)}';

    protected $description = 'Export unresolved WHMCS domains and unusual customer emails (non-gmail / .kgm) to xlsx for manual mapping';

    private const SUSPICIOUS = '/(\.kgm|@(gmail|yahoo|hotmail|outlook)\.com\.[a-z]+)$/i';

    public function handle(CustomerIdResolver $resolver): int
    {
        $path = $this->option('path') ?: storage_path('app/whmcs-mapping-' . now()->format('Ymd-His') . '.xlsx');

        $spreadsheet = new Spreadsheet();
        $this->sheet($spreadsheet, 0, 'Tên miền chưa có chủ', [
            'Tên miền', 'WHMCS domain id', 'Trạng thái', 'WHMCS userid (đã xoá)', 'Registrar', 'Hạn (nextduedate)', 'Email liên hệ còn lại', 'Gợi ý email đã sửa', 'Khách portal khớp gợi ý', 'EMAIL KHÁCH CẦN MAP (điền)',
        ], $this->unresolvedDomains($resolver));
        $this->sheet($spreadsheet, 1, 'Email lạ (.kgm)', [
            'Nguồn', 'ID', 'Họ tên', 'Công ty', 'Email hiện tại', 'Gợi ý email đã sửa', 'Khách portal khớp gợi ý', 'EMAIL ĐÚNG (điền)',
        ], $this->suspiciousEmails());
        $this->sheet($spreadsheet, 2, 'Email khác @gmail', [
            'Nguồn', 'ID', 'Họ tên', 'Công ty', 'Email', 'Tên miền email', 'Có trên portal', 'Có trên WHMCS',
        ], $this->nonGmailEmails());

        (new Xlsx($spreadsheet))->save($path);
        $this->info("Đã xuất: {$path}");

        return Command::SUCCESS;
    }

    private function unresolvedDomains(CustomerIdResolver $resolver): array
    {
        $whmcs = DB::connection('whmcs');
        $rows = [];
        $clientIds = $whmcs->table('tblclients')->pluck('id')->flip()->all();

        foreach ($whmcs->table('tbldomains')->orderBy('id')->cursor() as $domain) {
            if (isset($clientIds[$domain->userid]) || $resolver->resolveDomainOwner((string) $domain->domain, (int) $domain->userid) !== null) {
                continue;
            }

            $emails = $whmcs->table('tblcontacts')->where('userid', $domain->userid)->pluck('email')->filter()->all();
            $suggested = array_map(fn ($email) => $this->fixEmail($email), $emails);
            $rows[] = [
                trim($domain->domain), $domain->id, $domain->status, $domain->userid, $domain->registrar,
                $domain->nextduedate, implode('; ', $emails), implode('; ', $suggested), $this->portalMatches($suggested), '',
            ];
        }

        return $rows;
    }

    private function suspiciousEmails(): array
    {
        $rows = [];
        foreach ($this->people() as [$source, $id, $name, $company, $email]) {
            if (! preg_match(self::SUSPICIOUS, $email) && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                continue;
            }
            $fixed = $this->fixEmail($email);
            $rows[] = [$source, $id, $name, $company, $email, $fixed, $this->portalMatches([$fixed]), ''];
        }

        return $rows;
    }

    private function nonGmailEmails(): array
    {
        $portal = DB::table('customers')->pluck('id', DB::raw('LOWER(email)'))->all();
        $whmcsEmails = DB::connection('whmcs')->table('tblclients')->pluck('id', DB::raw('LOWER(email)'))->all();

        $rows = [];
        foreach ($this->people() as [$source, $id, $name, $company, $email]) {
            $domain = strtolower((string) substr(strrchr($email, '@') ?: '', 1));
            if ($domain === 'gmail.com') {
                continue;
            }
            $key = strtolower($email);
            $rows[] = [$source, $id, $name, $company, $email, $domain, isset($portal[$key]) ? "#{$portal[$key]}" : 'Không', isset($whmcsEmails[$key]) ? "#{$whmcsEmails[$key]}" : 'Không'];
        }
        usort($rows, fn ($a, $b) => [$a[5], $a[4]] <=> [$b[5], $b[4]]);

        return $rows;
    }

    /**
     * @return \Generator<int, array{0: string, 1: int, 2: ?string, 3: ?string, 4: string}>
     */
    private function people(): \Generator
    {
        foreach (DB::connection('whmcs')->table('tblclients')->orderBy('id')->get(['id', 'firstname', 'lastname', 'companyname', 'email']) as $client) {
            yield ['WHMCS khách', $client->id, trim("{$client->firstname} {$client->lastname}"), $client->companyname, trim((string) $client->email)];
        }
        foreach (DB::table('customers')->orderBy('id')->get(['id', 'fullname', 'company', 'email']) as $customer) {
            yield ['Portal khách', $customer->id, $customer->fullname, $customer->company, trim((string) $customer->email)];
        }
        foreach (DB::connection('whmcs')->table('tblcontacts')->orderBy('id')->get(['id', 'firstname', 'lastname', 'companyname', 'email']) as $contact) {
            yield ['WHMCS liên hệ', $contact->id, trim("{$contact->firstname} {$contact->lastname}"), $contact->companyname, trim((string) $contact->email)];
        }
    }

    private function fixEmail(string $email): string
    {
        return preg_replace('/(\.kgm|(?<=@gmail\.com|@yahoo\.com|@hotmail\.com|@outlook\.com)\.[a-z]+)$/i', '', trim($email)) ?? $email;
    }

    private function portalMatches(array $emails): string
    {
        $emails = array_values(array_filter(array_map('strtolower', $emails)));
        if ($emails === []) {
            return '';
        }

        return DB::table('customers')->whereIn(DB::raw('LOWER(email)'), $emails)->get(['id', 'email', 'fullname'])
            ->map(fn ($customer) => "#{$customer->id} {$customer->email} ({$customer->fullname})")->implode('; ');
    }

    private function sheet(Spreadsheet $spreadsheet, int $index, string $title, array $headers, array $rows): void
    {
        $sheet = $index === 0 ? $spreadsheet->getActiveSheet() : $spreadsheet->createSheet($index);
        $sheet->setTitle($title);

        foreach ($headers as $column => $header) {
            $sheet->setCellValueExplicit([$column + 1, 1], $header, DataType::TYPE_STRING);
        }
        $last = $sheet->getHighestColumn();
        $sheet->getStyle("A1:{$last}1")->getFont()->setBold(true);
        $sheet->getStyle("A1:{$last}1")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FED7AA');

        foreach ($rows as $rowIndex => $row) {
            foreach ($row as $column => $value) {
                $sheet->setCellValueExplicit([$column + 1, $rowIndex + 2], (string) ($value ?? ''), DataType::TYPE_STRING);
            }
        }

        foreach (range(1, count($headers)) as $column) {
            $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
        }
        $sheet->freezePane('A2');
        $sheet->setAutoFilter("A1:{$last}" . max(1, count($rows) + 1));
    }
}