File manager

File manager - Edit - /var/www/html/homologBancodetalentos/app/Support/TalentBank/LegacyMigrationDryRunAnalyzer.php

Back
<?php namespace App\Support\TalentBank; use Illuminate\Support\Facades\DB; use Illuminate\Support\Facades\Schema; use Throwable; /** * Análise somente leitura (dry-run) entre banco legado e banco alvo do BT novo. * Não grava dados — gera relatório de contagens e conflitos (CPF/CNPJ/e-mail). / final class LegacyMigrationDryRunAnalyzer { public function __construct( private string $legacyConnection, private string $targetConnection, /* @var array<string, string> / private array $tables ) { } /* * @return array<string, mixed> / public function analyze(): array { $warnings = []; $errors = []; if (! config("database.connections.{$this->legacyConnection}")) { $errors[] = "Conexão legada '{$this->legacyConnection}' não está definida em config/database.php."; return $this->wrapReport($warnings, $errors, [], []); } if (! config("database.connections.{$this->targetConnection}")) { $errors[] = "Conexão alvo '{$this->targetConnection}' não está definida."; return $this->wrapReport($warnings, $errors, [], []); } try { DB::connection($this->legacyConnection)->getPdo(); } catch (Throwable $e) { $errors[] = 'Falha ao conectar no banco legado: '.$e->getMessage(); return $this->wrapReport($warnings, $errors, [], []); } try { DB::connection($this->targetConnection)->getPdo(); } catch (Throwable $e) { $errors[] = 'Falha ao conectar no banco alvo: '.$e->getMessage(); return $this->wrapReport($warnings, $errors, [], []); } $legacyDb = (string) data_get(config("database.connections.{$this->legacyConnection}"), 'database'); $targetDb = (string) data_get(config("database.connections.{$this->targetConnection}"), 'database'); if ($legacyDb !== '' && $legacyDb === $targetDb && $this->legacyConnection === $this->targetConnection) { $warnings[] = 'Conexões legado e alvo apontam para o mesmo database na mesma conexão — o relatório pode não refletir dois ambientes distintos.'; } $summary = []; $conflicts = [ 'legacy_invalid_candidate_cpf' => [], 'legacy_duplicate_candidate_cpf' => [], 'legacy_duplicate_candidate_email' => [], 'legacy_invalid_company_cnpj' => [], 'legacy_duplicate_company_cnpj' => [], 'legacy_duplicate_company_email' => [], 'target_candidate_cpf_email_mismatch' => [], 'target_company_cnpj_email_mismatch' => [], 'orphan_job_applications' => [], 'orphan_jobs_company' => [], ]; $tCandidates = $this->tables['candidates']; $tCompanies = $this->tables['companies']; $tJobs = $this->tables['jobs']; $tPivot = $this->tables['job_applications']; if (! Schema::connection($this->legacyConnection)->hasTable($tCandidates)) { $warnings[] = "Tabela legada ausente: {$tCandidates}"; } else { $summary['legacy_candidates'] = (int) DB::connection($this->legacyConnection)->table($tCandidates)->count(); $this->analyzeCandidates($tCandidates, $conflicts); } if (! Schema::connection($this->legacyConnection)->hasTable($tCompanies)) { $warnings[] = "Tabela legada ausente: {$tCompanies}"; } else { $summary['legacy_companies'] = (int) DB::connection($this->legacyConnection)->table($tCompanies)->count(); $this->analyzeCompanies($tCompanies, $conflicts); } if (! Schema::connection($this->legacyConnection)->hasTable($tJobs)) { $warnings[] = "Tabela legada ausente: {$tJobs}"; } else { $summary['legacy_jobs'] = (int) DB::connection($this->legacyConnection)->table($tJobs)->count(); if (Schema::connection($this->legacyConnection)->hasTable($tCompanies)) { $this->analyzeOrphanJobs($tJobs, $tCompanies, $conflicts); } } if (! Schema::connection($this->legacyConnection)->hasTable($tPivot)) { $warnings[] = "Tabela legada ausente: {$tPivot}"; } else { $summary['legacy_job_applications'] = (int) DB::connection($this->legacyConnection)->table($tPivot)->count(); if (Schema::connection($this->legacyConnection)->hasTable($tJobs) && Schema::connection($this->legacyConnection)->hasTable($tCandidates)) { $this->analyzeOrphanPivot($tPivot, $tJobs, $tCandidates, $conflicts); } } if (Schema::connection($this->targetConnection)->hasTable($tCandidates)) { $summary['target_candidates'] = (int) DB::connection($this->targetConnection)->table($tCandidates)->count(); } else { $warnings[] = 'Tabela alvo candidates ausente — cruzamento de CPF/e-mail ignorado.'; } if (Schema::connection($this->targetConnection)->hasTable($tCompanies)) { $summary['target_companies'] = (int) DB::connection($this->targetConnection)->table($tCompanies)->count(); } else { $warnings[] = 'Tabela alvo companies ausente — cruzamento CNPJ/e-mail ignorado.'; } if (Schema::connection($this->targetConnection)->hasTable('job_listings')) { $summary['target_job_listings'] = (int) DB::connection($this->targetConnection)->table('job_listings')->count(); } return $this->wrapReport($warnings, $errors, $summary, $conflicts); } /* * @param array<string, mixed> $summary * @param array<string, array<int, array<string, mixed>>> $conflicts * @return array<string, mixed> / private function wrapReport(array $warnings, array $errors, array $summary = [], array $conflicts = []): array { $totals = []; foreach ($conflicts as $key => $rows) { if ($key === 'totals' \|\| ! is_array($rows)) { continue; } $totals[$key] = count($rows); } $conflicts['totals'] = $totals; return [ 'generated_at' => now()->toIso8601String(), 'legacy_connection' => $this->legacyConnection, 'target_connection' => $this->targetConnection, 'tables' => $this->tables, 'warnings' => $warnings, 'errors' => $errors, 'summary' => $summary, 'conflicts' => $conflicts, ]; } /* * @param array<string, array<int, mixed>> $conflicts / private function analyzeCandidates(string $table, array &$conflicts): void { $cpfMap = []; $emailMap = []; DB::connection($this->legacyConnection)->table($table)->orderBy('id')->chunk(1000, function ($rows) use (&$cpfMap, &$emailMap, &$conflicts): void { foreach ($rows as $row) { $id = (int) $row->id; $cpf = LegacyDocumentNormalizer::cpf((string) ($row->cpf ?? '')); if ($cpf === null) { $conflicts['legacy_invalid_candidate_cpf'][] = [ 'legacy_candidate_id' => $id, 'raw_cpf' => mb_substr((string) ($row->cpf ?? ''), 0, 32), ]; continue; } $cpfMap[$cpf][] = $id; $email = LegacyDocumentNormalizer::email($row->email ?? null); if ($email !== null) { $emailMap[$email][] = ['id' => $id, 'cpf' => $cpf]; } } }); foreach ($cpfMap as $cpf => $ids) { if (count($ids) > 1) { $conflicts['legacy_duplicate_candidate_cpf'][] = [ 'cpf' => $cpf, 'legacy_candidate_ids' => $ids, 'count' => count($ids), ]; } } foreach ($emailMap as $email => $entries) { if (count($entries) > 1) { $conflicts['legacy_duplicate_candidate_email'][] = [ 'email' => $email, 'legacy_candidate_ids' => array_column($entries, 'id'), 'cpfs' => array_values(array_unique(array_column($entries, 'cpf'))), ]; } } if (! Schema::connection($this->targetConnection)->hasTable($table)) { return; } DB::connection($this->legacyConnection)->table($table)->orderBy('id')->chunk(500, function ($rows) use ($table, &$conflicts): void { foreach ($rows as $row) { $cpf = LegacyDocumentNormalizer::cpf((string) ($row->cpf ?? '')); if ($cpf === null) { continue; } $legacyEmail = LegacyDocumentNormalizer::email($row->email ?? null); $target = DB::connection($this->targetConnection)->table($table)->where('cpf', $cpf)->first(); if (! $target \|\| $legacyEmail === null) { continue; } $targetEmail = LegacyDocumentNormalizer::email($target->email ?? null); if ($targetEmail !== null && $targetEmail !== $legacyEmail) { $conflicts['target_candidate_cpf_email_mismatch'][] = [ 'cpf' => $cpf, 'legacy_candidate_id' => (int) $row->id, 'legacy_email' => $legacyEmail, 'target_candidate_id' => (int) $target->id, 'target_email' => $targetEmail, ]; } } }); } /* * @param array<string, array<int, mixed>> $conflicts / private function analyzeCompanies(string $table, array &$conflicts): void { $cnpjMap = []; $emailMap = []; DB::connection($this->legacyConnection)->table($table)->orderBy('id')->chunk(500, function ($rows) use (&$cnpjMap, &$emailMap, &$conflicts): void { foreach ($rows as $row) { $id = (int) $row->id; $cnpj = LegacyDocumentNormalizer::cnpj($row->cnpj ?? ''); if ($cnpj === null) { $conflicts['legacy_invalid_company_cnpj'][] = [ 'legacy_company_id' => $id, 'raw_cnpj' => (string) ($row->cnpj ?? ''), ]; continue; } $cnpjMap[$cnpj][] = $id; if (isset($row->email)) { $email = LegacyDocumentNormalizer::email((string) $row->email); if ($email !== null) { $emailMap[$email][] = ['id' => $id, 'cnpj' => $cnpj]; } } } }); foreach ($cnpjMap as $cnpj => $ids) { if (count($ids) > 1) { $conflicts['legacy_duplicate_company_cnpj'][] = [ 'cnpj' => $cnpj, 'legacy_company_ids' => $ids, ]; } } foreach ($emailMap as $email => $entries) { if (count($entries) > 1) { $conflicts['legacy_duplicate_company_email'][] = [ 'email' => $email, 'legacy_company_ids' => array_column($entries, 'id'), 'cnpjs' => array_values(array_unique(array_column($entries, 'cnpj'))), ]; } } if (! Schema::connection($this->targetConnection)->hasTable($table)) { return; } DB::connection($this->legacyConnection)->table($table)->orderBy('id')->chunk(500, function ($rows) use ($table, &$conflicts): void { foreach ($rows as $row) { $cnpj = LegacyDocumentNormalizer::cnpj($row->cnpj ?? ''); if ($cnpj === null \|\| ! isset($row->email)) { continue; } $legacyEmail = LegacyDocumentNormalizer::email((string) $row->email); if ($legacyEmail === null) { continue; } $target = DB::connection($this->targetConnection)->table($table)->where('cnpj', $cnpj)->first(); if (! $target) { continue; } $targetEmail = LegacyDocumentNormalizer::email($target->email ?? null); if ($targetEmail !== null && $targetEmail !== $legacyEmail) { $conflicts['target_company_cnpj_email_mismatch'][] = [ 'cnpj' => $cnpj, 'legacy_company_id' => (int) $row->id, 'legacy_email' => $legacyEmail, 'target_company_id' => (int) $target->id, 'target_email' => $targetEmail, ]; } } }); } /* * @param array<string, array<int, mixed>> $conflicts / private function analyzeOrphanJobs(string $jobsTable, string $companiesTable, array &$conflicts): void { $orphans = DB::connection($this->legacyConnection) ->table($jobsTable.' as j') ->leftJoin($companiesTable.' as c', 'j.company_id', '=', 'c.id') ->whereNull('c.id') ->select('j.id as job_id', 'j.company_id') ->limit(5000) ->get(); foreach ($orphans as $o) { $conflicts['orphan_jobs_company'][] = [ 'legacy_job_id' => (int) $o->job_id, 'legacy_company_id' => (int) $o->company_id, ]; } } /* * @param array<string, array<int, mixed>> $conflicts */ private function analyzeOrphanPivot(string $pivotTable, string $jobsTable, string $candidatesTable, array &$conflicts): void { $q1 = DB::connection($this->legacyConnection) ->table($pivotTable.' as ja') ->leftJoin($jobsTable.' as j', 'ja.job_id', '=', 'j.id') ->whereNull('j.id') ->select('ja.job_id', 'ja.candidate_id') ->limit(5000) ->get(); foreach ($q1 as $o) { $conflicts['orphan_job_applications'][] = [ 'type' => 'missing_job', 'legacy_job_id' => (int) $o->job_id, 'legacy_candidate_id' => (int) $o->candidate_id, ]; } $q2 = DB::connection($this->legacyConnection) ->table($pivotTable.' as ja') ->leftJoin($candidatesTable.' as c', 'ja.candidate_id', '=', 'c.id') ->whereNull('c.id') ->select('ja.job_id', 'ja.candidate_id') ->limit(5000) ->get(); foreach ($q2 as $o) { $conflicts['orphan_job_applications'][] = [ 'type' => 'missing_candidate', 'legacy_job_id' => (int) $o->job_id, 'legacy_candidate_id' => (int) $o->candidate_id, ]; } } }

| ver. 1.4 | Github | . | PHP 8.3.28 | Generation time: 0.02 | proxy | phpinfo | Settings