.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 */ 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)); } }