File manager

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

Back
<?php namespace App\Support\TalentBank; use Carbon\Carbon; use Illuminate\Database\Query\Builder; use Illuminate\Support\Facades\DB; use Illuminate\Support\Facades\Schema; use Throwable; /** * Migração idempotente legado→BT novo: candidates, companies, job_listings, pivô candidaturas. * * Chaves de idempotência: * - candidates: CPF (string 11 dígitos) * - companies: CNPJ (string 14 dígitos) * - job_listings: legacy_id (ID da tabela jobs do legado) * - job_listing_candidate: (job_listing_id, candidate_id) — unique constraint existente * * Nenhum dado é apagado; re-execuções são seguras. * * Progresso: * Passe um callable via setProgressCallback() para receber notificações a cada chunk. * Assinatura: function(string $domain, int $processed, int $total, int $inserted, int $updated, int $skipped, int $errors): void / final class LegacyMigrationImporter { /* @var array<string, array{inserted:int, updated:int, skipped:int, errors:list<array<string, mixed>>}> / private array $stats; /* @var array<string, int>\|null Mapa nome-normalizado → ethnicity_id (lazy-loaded) / private ?array $ethnicityNameMap = null; /* @var array<string, array<int, true>> Cache de IDs válidos por tabela alvo / private array $validIdCache = []; /* @var callable\|null / private $progressCallback = null; public function __construct( private readonly string $legacyConnection, private readonly string $targetConnection, /* @var array<string, string> / private readonly array $tables, private readonly ?string $fromDate = null, private readonly int $batchSize = 500, ) { foreach (['candidates', 'companies', 'job_listings', 'job_applications'] as $domain) { $this->stats[$domain] = ['inserted' => 0, 'updated' => 0, 'skipped' => 0, 'errors' => []]; } } /* * Define um callback chamado a cada chunk processado. * * @param callable(string $domain, int $processed, int $total, int $inserted, int $updated, int $skipped, int $errors): void $callback / public function setProgressCallback(callable $callback): static { $this->progressCallback = $callback; return $this; } /* Conta total de registros de uma tabela legada (para progresso); retorna 0 se indisponível. / public function countLegacy(string $domain): int { $table = $this->tables[$domain] ?? null; if ($table === null) { return 0; } try { return (int) DB::connection($this->legacyConnection)->table($table)->count(); } catch (Throwable) { return 0; } } private function reportProgress(string $domain, int $processed, int $total): void { if ($this->progressCallback === null) { return; } $s = $this->stats[$domain]; ($this->progressCallback)( $domain, $processed, $total, $s['inserted'], $s['updated'], $s['skipped'], count($s['errors']), ); } /* @return array<string, mixed> / public function importAll(): array { $this->importCandidates(); $this->importCompanies(); $this->importJobs(); $this->importApplications(); return $this->buildReport('all'); } /* @return array<string, mixed> / public function importCandidates(): array { $legacyTable = $this->tables['candidates']; if (! Schema::connection($this->legacyConnection)->hasTable($legacyTable)) { $this->stats['candidates']['errors'][] = [ 'id' => null, 'message' => "Tabela legada '{$legacyTable}' não encontrada.", ]; return $this->buildReport('candidates'); } $query = DB::connection($this->legacyConnection)->table($legacyTable)->orderBy('id'); $this->applyFromDate($query, $legacyTable); $total = $this->countLegacy('candidates'); $processed = 0; $this->reportProgress('candidates', 0, $total); $query->chunk($this->batchSize, function ($rows) use ($total, &$processed): void { $batch = []; foreach ($rows as $row) { $cpf = $this->normalizeLegacyCpf($row->cpf ?? null); if ($cpf === null) { $this->stats['candidates']['skipped']++; continue; } $batch[$cpf] = $this->mapCandidate($row, $cpf); } $processed += count($rows); if (empty($batch)) { $this->reportProgress('candidates', $processed, $total); return; } $existingCpfs = DB::connection($this->targetConnection) ->table('candidates') ->whereIn('cpf', array_keys($batch)) ->pluck('cpf') ->flip() ->toArray(); $now = now()->toDateTimeString(); foreach ($batch as $cpf => $data) { try { if (isset($existingCpfs[$cpf])) { DB::connection($this->targetConnection) ->table('candidates') ->where('cpf', $cpf) ->update(array_merge($data, ['updated_at' => $now])); $this->stats['candidates']['updated']++; } else { DB::connection($this->targetConnection) ->table('candidates') ->insert(array_merge($data, ['created_at' => $now, 'updated_at' => $now])); $this->stats['candidates']['inserted']++; } } catch (Throwable $e) { $this->stats['candidates']['errors'][] = [ 'cpf' => $cpf, 'message' => $e->getMessage(), ]; } } $this->reportProgress('candidates', $processed, $total); }); return $this->buildReport('candidates'); } /* @return array<string, mixed> / public function importCompanies(): array { $legacyTable = $this->tables['companies']; if (! Schema::connection($this->legacyConnection)->hasTable($legacyTable)) { $this->stats['companies']['errors'][] = [ 'id' => null, 'message' => "Tabela legada '{$legacyTable}' não encontrada.", ]; return $this->buildReport('companies'); } $query = DB::connection($this->legacyConnection)->table($legacyTable)->orderBy('id'); $this->applyFromDate($query, $legacyTable); $total = $this->countLegacy('companies'); $processed = 0; $this->reportProgress('companies', 0, $total); $query->chunk($this->batchSize, function ($rows) use ($total, &$processed): void { $batch = []; foreach ($rows as $row) { $cnpj = LegacyDocumentNormalizer::cnpj($row->cnpj ?? null); if ($cnpj === null) { $this->stats['companies']['skipped']++; continue; } $batch[$cnpj] = $this->mapCompany($row, $cnpj); } $processed += count($rows); if (empty($batch)) { $this->reportProgress('companies', $processed, $total); return; } $existingCnpjs = DB::connection($this->targetConnection) ->table('companies') ->whereIn('cnpj', array_keys($batch)) ->pluck('cnpj') ->flip() ->toArray(); $now = now()->toDateTimeString(); foreach ($batch as $cnpj => $data) { try { if (isset($existingCnpjs[$cnpj])) { DB::connection($this->targetConnection) ->table('companies') ->where('cnpj', $cnpj) ->update(array_merge($data, ['updated_at' => $now])); $this->stats['companies']['updated']++; } else { DB::connection($this->targetConnection) ->table('companies') ->insert(array_merge($data, ['created_at' => $now, 'updated_at' => $now])); $this->stats['companies']['inserted']++; } } catch (Throwable $e) { $this->stats['companies']['errors'][] = [ 'cnpj' => $cnpj, 'message' => $e->getMessage(), ]; } } $this->reportProgress('companies', $processed, $total); }); return $this->buildReport('companies'); } /* @return array<string, mixed> / public function importJobs(): array { $legacyTable = $this->tables['jobs']; if (! Schema::connection($this->legacyConnection)->hasTable($legacyTable)) { $this->stats['job_listings']['errors'][] = [ 'id' => null, 'message' => "Tabela legada '{$legacyTable}' não encontrada.", ]; return $this->buildReport('job_listings'); } $query = DB::connection($this->legacyConnection)->table($legacyTable)->orderBy('id'); $this->applyFromDate($query, $legacyTable); $total = $this->countLegacy('jobs'); $processed = 0; $this->reportProgress('job_listings', 0, $total); $query->chunk($this->batchSize, function ($rows) use ($total, &$processed): void { $legacyCompanyIds = collect($rows)->pluck('company_id')->filter()->unique()->values()->all(); $companyMap = $this->buildCompanyMap($legacyCompanyIds); $batch = []; foreach ($rows as $row) { $legacyJobId = (int) $row->id; $newCompanyId = $companyMap[(int) ($row->company_id ?? 0)] ?? null; if ($newCompanyId === null) { $this->stats['job_listings']['skipped']++; continue; } $data = $this->mapJob($row, $legacyJobId, $newCompanyId); if ($data === null) { $this->stats['job_listings']['skipped']++; continue; } $batch[$legacyJobId] = $data; } $processed += count($rows); if (empty($batch)) { $this->reportProgress('job_listings', $processed, $total); return; } $existingLegacyIds = DB::connection($this->targetConnection) ->table('job_listings') ->whereIn('legacy_id', array_keys($batch)) ->pluck('legacy_id') ->flip() ->toArray(); $now = now()->toDateTimeString(); foreach ($batch as $legacyJobId => $data) { try { if (isset($existingLegacyIds[$legacyJobId])) { $updateData = $data; unset($updateData['legacy_id'], $updateData['company_id']); DB::connection($this->targetConnection) ->table('job_listings') ->where('legacy_id', $legacyJobId) ->update(array_merge($updateData, ['updated_at' => $now])); $this->stats['job_listings']['updated']++; } else { DB::connection($this->targetConnection) ->table('job_listings') ->insert(array_merge($data, ['created_at' => $now, 'updated_at' => $now])); $this->stats['job_listings']['inserted']++; } } catch (Throwable $e) { $this->stats['job_listings']['errors'][] = [ 'legacy_job_id' => $legacyJobId, 'message' => $e->getMessage(), ]; } } $this->reportProgress('job_listings', $processed, $total); }); return $this->buildReport('job_listings'); } /* @return array<string, mixed> / public function importApplications(): array { $legacyTable = $this->tables['job_applications']; if (! Schema::connection($this->legacyConnection)->hasTable($legacyTable)) { $this->stats['job_applications']['errors'][] = [ 'id' => null, 'message' => "Tabela legada '{$legacyTable}' não encontrada.", ]; return $this->buildReport('job_applications'); } // Tabela job_applications do legado não tem timestamps — filtro de data não se aplica. $total = $this->countLegacy('job_applications'); $processed = 0; $this->reportProgress('job_applications', 0, $total); DB::connection($this->legacyConnection) ->table($legacyTable) ->orderBy('id') ->chunk($this->batchSize, function ($rows) use ($total, &$processed): void { $legacyCandidateIds = collect($rows)->pluck('candidate_id')->filter()->unique()->values()->all(); $legacyJobIds = collect($rows)->pluck('job_id')->filter()->unique()->values()->all(); $candidateMap = $this->buildCandidateMap($legacyCandidateIds); $jobListingMap = $this->buildJobListingMap($legacyJobIds); $now = now()->toDateTimeString(); foreach ($rows as $row) { $newCandidateId = $candidateMap[(int) ($row->candidate_id ?? 0)] ?? null; $newJobListingId = $jobListingMap[(int) ($row->job_id ?? 0)] ?? null; if ($newCandidateId === null \|\| $newJobListingId === null) { $this->stats['job_applications']['skipped']++; continue; } try { $exists = DB::connection($this->targetConnection) ->table('job_listing_candidate') ->where('job_listing_id', $newJobListingId) ->where('candidate_id', $newCandidateId) ->exists(); if ($exists) { $this->stats['job_applications']['updated']++; continue; } $insert = [ 'job_listing_id' => $newJobListingId, 'candidate_id' => $newCandidateId, 'created_at' => $now, 'updated_at' => $now, ]; if (Schema::connection($this->targetConnection)->hasColumn('job_listing_candidate', 'status')) { $insert['status'] = JobApplicationStatus::ENROLLED; } DB::connection($this->targetConnection) ->table('job_listing_candidate') ->insert($insert); $this->stats['job_applications']['inserted']++; } catch (Throwable $e) { $this->stats['job_applications']['errors'][] = [ 'legacy_job_id' => (int) ($row->job_id ?? 0), 'legacy_candidate_id' => (int) ($row->candidate_id ?? 0), 'message' => $e->getMessage(), ]; } } $processed += count($rows); $this->reportProgress('job_applications', $processed, $total); }); return $this->buildReport('job_applications'); } /* @return array<string, mixed> / public function buildReport(string $domain): array { $stats = $domain === 'all' ? $this->stats : [$domain => $this->stats[$domain] ?? []]; $totalErrors = (int) array_sum(array_map( static fn (array $s): int => count($s['errors'] ?? []), $stats )); return [ 'generated_at' => now()->toIso8601String(), 'legacy_connection' => $this->legacyConnection, 'target_connection' => $this->targetConnection, 'domain' => $domain, 'from_date' => $this->fromDate, 'stats' => $stats, 'total_errors' => $totalErrors, ]; } // ------------------------------------------------------------------------- // Mapeadores de campos // ------------------------------------------------------------------------- /* @return array<string, mixed> / private function mapCandidate(object $row, string $cpf): array { return [ 'cpf' => $cpf, 'name' => (string) ($row->name ?? ''), 'email' => LegacyDocumentNormalizer::email($row->email ?? null) ?? '', 'address' => $this->nullableString($row->address ?? null), 'zip_code' => $this->nullableString($row->zip_code ?? null), 'birth_date' => $this->nullableDate($row->birth_date ?? null), 'special_needs' => $this->normalizeBool($row->special_needs ?? false), 'telephone' => $this->nullablePhone($row->telephone ?? null), 'cellphone' => $this->nullablePhone($row->cellphone ?? null), 'min_payment' => $this->cappedPayment($row->min_payment ?? null), 'desired_payment' => $this->cappedPayment($row->desired_payment ?? null), 'cv_summary' => $this->nullableString($row->cv_summary ?? null), 'has_experience' => isset($row->has_experience) ? $this->normalizeBool($row->has_experience) : null, 'active' => $this->normalizeBool($row->active ?? true), 'neighbourhood_id' => $this->validateFk('neighbourhoods', $this->nullableInt($row->neighbourhood_id ?? null)), 'gender_id' => $this->validateFk('genders', $this->nullableInt($row->gender_id ?? null)), 'marital_status_id' => $this->validateFk('marital_statuses', $this->nullableInt($row->marital_status_id ?? null)), 'occupation_id' => $this->validateFk('occupations', $this->nullableInt($row->occupation_id ?? null)), 'ethnicity_id' => $this->resolveEthnicityId($row), ]; } /* * Resolve ethnicity_id a partir do registro legado. * O legado pode ter: * - ethnicity_id (int) — bancos já normalizados * - ethnicity (string) — banco original (siati_app.sql) * Retorna o ID na tabela ethnicities do sistema novo, ou null. / private function resolveEthnicityId(object $row): ?int { // Banco legado já normalizado com FK if (isset($row->ethnicity_id) && $row->ethnicity_id !== null && $row->ethnicity_id !== '') { return $this->nullableInt($row->ethnicity_id); } // Banco original com string inline if (isset($row->ethnicity) && $row->ethnicity !== null && trim($row->ethnicity) !== '') { $map = $this->getEthnicityNameMap(); $key = mb_strtolower(trim((string) $row->ethnicity)); return $map[$key] ?? null; } return null; } /* @return array<string, int> / private function getEthnicityNameMap(): array { if ($this->ethnicityNameMap !== null) { return $this->ethnicityNameMap; } $this->ethnicityNameMap = []; DB::connection($this->targetConnection) ->table('ethnicities') ->get(['id', 'name']) ->each(function (object $row): void { $key = mb_strtolower(trim($row->name)); $this->ethnicityNameMap[$key] = (int) $row->id; }); return $this->ethnicityNameMap; } /* * Retorna $id se ele existir na $table do banco alvo, ou null. * Carrega e cacheia todos os IDs da tabela na primeira chamada. / /* * Retorna $id se ele existir na $table do banco alvo, ou null. * Carrega e cacheia todos os IDs da tabela na primeira chamada — tabelas de referência * são pequenas (< 10k linhas), tornando o cache em memória seguro. / private function validateFk(string $table, ?int $id): ?int { if ($id === null) { return null; } if (! isset($this->validIdCache[$table])) { $this->validIdCache[$table] = []; $ids = DB::connection($this->targetConnection)->table($table)->pluck('id'); foreach ($ids as $i) { $this->validIdCache[$table][(int) $i] = true; } } return isset($this->validIdCache[$table][$id]) ? $id : null; } /* @return array<string, mixed> / private function mapCompany(object $row, string $cnpj): array { return [ 'cnpj' => $cnpj, 'corporate_name' => $this->nullableString($row->corporate_name ?? null), 'trading_name' => $this->nullableString($row->trading_name ?? null), 'occupation_area' => $this->nullableString($row->occupation_area ?? null), 'county' => $this->nullableString($row->county ?? null), 'cnae' => $this->nullableString($row->cnae ?? null), 'logo' => $this->nullableString($row->logo ?? null), 'description' => $this->nullableString($row->description ?? null), 'address' => $this->nullableString($row->address ?? null), 'zip_code' => $this->nullableString($row->zip_code ?? null), 'neighbourhood_id' => $this->validateFk('neighbourhoods', $this->nullableInt($row->neighbourhood_id ?? null)), 'telephone' => $this->nullablePhone($row->telephone ?? null), 'cellphone' => $this->nullablePhone($row->cellphone ?? null), 'contact_name' => $this->nullableString($row->contact_name ?? null), 'email' => LegacyDocumentNormalizer::email($row->email ?? null), 'module_id' => $this->nullableString($row->module_id ?? null), 'module_name' => $this->nullableString($row->module_name ?? null), 'active' => $this->normalizeBool($row->active ?? true), ]; } /* * Retorna null se campos NOT NULL obrigatórios (occupation_id, salary_range_id, contract_type_id) estiverem ausentes. * * Nota: campo 'descripton' (typo) no legado é mapeado para 'description' no novo sistema. * * @return array<string, mixed>\|null / private function mapJob(object $row, int $legacyJobId, int $newCompanyId): ?array { $occupationId = $this->validateFk('occupations', $this->nullableInt($row->occupation_id ?? null)); $salaryRangeId = $this->validateFk('salary_ranges', $this->nullableInt($row->salary_range_id ?? null)); $contractTypeId = $this->validateFk('contract_types', $this->nullableInt($row->contract_type_id ?? null)); if ($occupationId === null \|\| $salaryRangeId === null \|\| $contractTypeId === null) { return null; } // Legacy tem typo 'descripton'; lê 'description' se existir, senão 'descripton'. $description = (string) ($row->description ?? $row->descripton ?? ''); return [ 'legacy_id' => $legacyJobId, 'company_id' => $newCompanyId, 'occupation_id' => $occupationId, 'salary_range_id' => $salaryRangeId, 'contract_type_id' => $contractTypeId, 'neighbourhood_id' => $this->validateFk('neighbourhoods', $this->nullableInt($row->neighbourhood_id ?? null)), 'expertise_level_id' => $this->validateFk('expertise_levels', $this->nullableInt($row->expertise_level_id ?? null)), 'modality_id' => $this->validateFk('modalities', $this->nullableInt($row->modality_id ?? null)), 'workload_id' => $this->validateFk('workloads', $this->nullableInt($row->workload_id ?? null)), 'spots_total' => max(0, (int) ($row->spots_total ?? 0)), 'spots_filled' => max(0, (int) ($row->spots_filled ?? 0)), 'description' => $description, 'requirements_mandatory' => $this->nullableString($row->requirements_mandatory ?? null), 'requirements_optional' => $this->nullableString($row->requirements_optional ?? null), 'benefits' => $this->nullableString($row->benefits ?? null), 'misc_info' => $this->nullableString($row->misc_info ?? null), 'hide_company' => $this->normalizeBool($row->hide_company ?? false), 'is_hiring' => $this->normalizeBool($row->is_hiring ?? true), ]; } // ------------------------------------------------------------------------- // Mapas de referência cruzada (cross-connection, por chunk) // ------------------------------------------------------------------------- /* * Mapa legacy_company_id → new_company_id via CNPJ. * * @param int[] $legacyCompanyIds * @return array<int, int> / private function buildCompanyMap(array $legacyCompanyIds): array { if (empty($legacyCompanyIds)) { return []; } $legacyRows = DB::connection($this->legacyConnection) ->table($this->tables['companies']) ->whereIn('id', $legacyCompanyIds) ->get(['id', 'cnpj']); $idToCnpj = []; $cnpjs = []; foreach ($legacyRows as $row) { $cnpj = LegacyDocumentNormalizer::cnpj($row->cnpj ?? null); if ($cnpj !== null) { $idToCnpj[(int) $row->id] = $cnpj; $cnpjs[] = $cnpj; } } if (empty($cnpjs)) { return []; } $newRows = DB::connection($this->targetConnection) ->table('companies') ->whereIn('cnpj', $cnpjs) ->get(['id', 'cnpj']); $cnpjToNewId = []; foreach ($newRows as $row) { $cnpjToNewId[(string) $row->cnpj] = (int) $row->id; } $map = []; foreach ($idToCnpj as $legacyId => $cnpj) { if (isset($cnpjToNewId[$cnpj])) { $map[$legacyId] = $cnpjToNewId[$cnpj]; } } return $map; } /* * Mapa legacy_candidate_id → new_candidate_id via CPF. * * @param int[] $legacyCandidateIds * @return array<int, int> / private function buildCandidateMap(array $legacyCandidateIds): array { if (empty($legacyCandidateIds)) { return []; } $legacyRows = DB::connection($this->legacyConnection) ->table($this->tables['candidates']) ->whereIn('id', $legacyCandidateIds) ->get(['id', 'cpf']); $idToCpf = []; $cpfs = []; foreach ($legacyRows as $row) { $cpf = $this->normalizeLegacyCpf($row->cpf ?? null); if ($cpf !== null) { $idToCpf[(int) $row->id] = $cpf; $cpfs[] = $cpf; } } if (empty($cpfs)) { return []; } $newRows = DB::connection($this->targetConnection) ->table('candidates') ->whereIn('cpf', $cpfs) ->get(['id', 'cpf']); $cpfToNewId = []; foreach ($newRows as $row) { $cpfToNewId[(string) $row->cpf] = (int) $row->id; } $map = []; foreach ($idToCpf as $legacyId => $cpf) { if (isset($cpfToNewId[$cpf])) { $map[$legacyId] = $cpfToNewId[$cpf]; } } return $map; } /* * Mapa legacy_job_id → new_job_listing_id via job_listings.legacy_id. * * @param int[] $legacyJobIds * @return array<int, int> / private function buildJobListingMap(array $legacyJobIds): array { if (empty($legacyJobIds)) { return []; } $rows = DB::connection($this->targetConnection) ->table('job_listings') ->whereIn('legacy_id', $legacyJobIds) ->get(['id', 'legacy_id']); $map = []; foreach ($rows as $row) { $map[(int) $row->legacy_id] = (int) $row->id; } return $map; } // ------------------------------------------------------------------------- // Helpers de normalização // ------------------------------------------------------------------------- /* * Normaliza CPF legado: suporta bigInteger (sem zero à esquerda) e string. / private function normalizeLegacyCpf(mixed $raw): ?string { $digits = preg_replace('/\D/', '', (string) $raw); if ($digits === '' \|\| $digits === '0') { return null; } if (strlen($digits) < 11) { $digits = str_pad($digits, 11, '0', STR_PAD_LEFT); } return strlen($digits) === 11 ? $digits : null; } private function normalizeBool(mixed $val): bool { if (is_bool($val)) { return $val; } if (is_int($val) \|\| is_float($val)) { return (int) $val !== 0; } $s = strtolower(trim((string) $val)); return in_array($s, ['1', 'true', 's', 'sim', 'a', 'aberta'], true); } private function nullableString(mixed $val): ?string { if ($val === null) { return null; } $s = trim((string) $val); return ($s === '' \|\| strtoupper($s) === 'NULL') ? null : $s; } private function nullableInt(mixed $val): ?int { if ($val === null \|\| $val === '') { return null; } $i = (int) $val; return $i === 0 ? null : $i; } private function nullableDate(mixed $val): ?string { $s = $this->nullableString($val); if ($s === null) { return null; } try { return Carbon::parse($s)->toDateString(); } catch (Throwable) { return null; } } /* Telefone/celular: remove não-dígitos; retorna null se vazio (bigInteger legado pode ser 0). */ private function nullablePhone(mixed $val): ?string { $digits = preg_replace('/\D/', '', (string) $val); return ($digits === '' \|\| $digits === '0') ? null : $digits; } private function cappedPayment(mixed $val): ?float { if ($val === null \|\| $val === '') { return null; } $f = (float) $val; // Novo sistema usa decimal(12,2) — suporta até 9.999.999.999,99 return $f > 0 ? min($f, 9999999999.99) : null; } private function applyFromDate(Builder $query, string $table): void { if ($this->fromDate === null) { return; } $cols = Schema::connection($this->legacyConnection)->getColumnListing($table); if (in_array('updated_at', $cols, true)) { $query->where('updated_at', '>=', $this->fromDate); } elseif (in_array('created_at', $cols, true)) { $query->where('created_at', '>=', $this->fromDate); } } }

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