File manager

File manager - Edit - /var/www/html/portal/app/Console/Commands/ImportLegislacaoBoletinsCommand.php

Back
<?php namespace App\Console\Commands; use App\Support\LegislacaoImportPaths; use Illuminate\Console\Command; use Illuminate\Support\Facades\DB; class ImportLegislacaoBoletinsCommand extends Command { protected $signature = 'legislacao:import-boletins {--path= : Pasta com os CSVs de legislacao (padrao: temp/Legislacao-27052026 ou temp/Legislacao)} {--dry-run : Simula sem gravar no banco}'; protected $description = 'Vincula boletins oficiais (UUID) aos registros de legislacao importados com IDs legados inteiros.'; private string $basePath; /** @var array<string, array<string, object>> / private array $bulletinsByDate = []; /* @var array<string, string> legacy_id => bulletin uuid / private array $legacyToUuid = []; /* @var list<string> / private array $unresolvedLegacyIds = []; public function handle(): int { set_time_limit(0); $this->basePath = $this->resolveBasePath((string) $this->option('path')); if (! is_dir($this->basePath)) { $this->error("Pasta nao encontrada: {$this->basePath}"); return self::FAILURE; } $dryRun = (bool) $this->option('dry-run'); $this->info('Carregando boletins oficiais...'); $this->loadBulletinsByDate(); $this->info('Construindo mapa legado (inteiro) -> boletim (UUID)...'); $legacyDates = $this->buildLegacyPublicationDates(); $this->buildLegacyToUuidMap($legacyDates); $this->line(sprintf( 'Mapa: %d resolvidos, %d sem correspondencia (de %d IDs legados).', count($this->legacyToUuid), count($this->unresolvedLegacyIds), count($legacyDates) )); if ($this->unresolvedLegacyIds !== []) { $sample = array_slice($this->unresolvedLegacyIds, 0, 15); $this->warn('IDs legados sem boletim: '.implode(', ', $sample).( count($this->unresolvedLegacyIds) > 15 ? ' ...' : '' )); } $tables = [ 'legislacao' => ['csv' => 'LEGISLACAO.csv', 'pk' => 'id_legislacao'], 'legislacao_portaria' => ['csv' => 'LEGISLACAO_PORTARIA.csv', 'pk' => 'id_portaria'], 'legislacao_retificacao' => ['csv' => 'LEGISLACAO_RETIFICACAO.csv', 'pk' => 'id_retificacao'], 'legislacao_portaria_retificacao' => [ 'csv' => 'LEGISLACAO_PORTARIA_RETIFICACAO.csv', 'pk' => 'id_retificacao', ], ]; $totalUpdated = 0; $rows = []; foreach ($tables as $table => $config) { $stats = $this->applyMappingToTable( $table, $config['csv'], $config['pk'], $dryRun ); $totalUpdated += $stats['updated']; $rows[] = [ $table, $stats['csv_rows'], $stats['updated'], $stats['skipped'], $stats['unmapped'], ]; } $this->newLine(); $this->table( ['Tabela', 'Linhas CSV c/ BO', 'Atualizados', 'Ja vinculados', 'Sem mapa'], $rows ); if ($dryRun) { $this->warn('Dry-run: nenhuma alteracao foi gravada.'); } else { $this->info("Importacao concluida. {$totalUpdated} registro(s) atualizado(s)."); } return self::SUCCESS; } private function resolveBasePath(string $path): string { return LegislacaoImportPaths::resolveBasePath($path !== '' ? $path : null); } private function loadBulletinsByDate(): void { foreach (DB::table('bulletins') ->select('id', 'publicationDate', 'extraordinaryEdition') ->cursor() as $bulletin) { $date = substr((string) $bulletin->publicationDate, 0, 10); $this->bulletinsByDate[$date][] = $bulletin; } } /* * @return array<string, array<string, int>> legacy_id => [date => count] / private function buildLegacyPublicationDates(): array { $legacyDates = []; $this->collectLegacyDatesFromCsv( 'LEGISLACAO.csv', 'id_boletim_oficial', 'dt_publicacao', $legacyDates ); $this->collectLegacyDatesFromCsv( 'LEGISLACAO_PORTARIA.csv', 'id_boletim_oficial', 'dt_publicacao', $legacyDates ); $legislacaoDates = $this->loadParentDates('LEGISLACAO.csv', 'id_legislacao', 'dt_publicacao'); $portariaDates = $this->loadParentDates('LEGISLACAO_PORTARIA.csv', 'id_portaria', 'dt_publicacao'); $this->collectLegacyDatesFromParentCsv( 'LEGISLACAO_RETIFICACAO.csv', 'id_boletim_oficial', 'id_legislacao', $legislacaoDates, $legacyDates ); $this->collectLegacyDatesFromParentCsv( 'LEGISLACAO_PORTARIA_RETIFICACAO.csv', 'id_boletim_oficial', 'id_portaria', $portariaDates, $legacyDates ); return $legacyDates; } /* * @param array<string, array<string, int>> $legacyDates / private function collectLegacyDatesFromCsv( string $filename, string $boColumn, string $dateColumn, array &$legacyDates ): void { foreach ($this->readCsvRows($filename) as $row) { $legacyId = trim((string) ($row[$boColumn] ?? '')); $date = trim((string) ($row[$dateColumn] ?? '')); if ($legacyId === '' \|\| ! is_numeric($legacyId) \|\| $date === '') { continue; } $legacyDates[$legacyId][$date] = ($legacyDates[$legacyId][$date] ?? 0) + 1; } } /* * @return array<string, string> / private function loadParentDates(string $filename, string $idColumn, string $dateColumn): array { $dates = []; foreach ($this->readCsvRows($filename) as $row) { $id = trim((string) ($row[$idColumn] ?? '')); $date = trim((string) ($row[$dateColumn] ?? '')); if ($id !== '' && $date !== '') { $dates[$id] = $date; } } return $dates; } /* * @param array<string, string> $parentDates * @param array<string, array<string, int>> $legacyDates / private function collectLegacyDatesFromParentCsv( string $filename, string $boColumn, string $parentColumn, array $parentDates, array &$legacyDates ): void { foreach ($this->readCsvRows($filename) as $row) { $legacyId = trim((string) ($row[$boColumn] ?? '')); $parentId = trim((string) ($row[$parentColumn] ?? '')); if ($legacyId === '' \|\| ! is_numeric($legacyId)) { continue; } $date = $parentDates[$parentId] ?? ''; if ($date === '') { continue; } $legacyDates[$legacyId][$date] = ($legacyDates[$legacyId][$date] ?? 0) + 1; } } /* * @param array<string, array<string, int>> $legacyDates / private function buildLegacyToUuidMap(array $legacyDates): void { foreach ($legacyDates as $legacyId => $dates) { arsort($dates); $bestDate = (string) array_key_first($dates); $uuid = $this->resolveBulletinUuidByDate($bestDate); if ($uuid !== null) { $this->legacyToUuid[(string) $legacyId] = $uuid; } else { $this->unresolvedLegacyIds[] = (string) $legacyId; } } } private function resolveBulletinUuidByDate(string $date): ?string { $uuid = $this->pickUniqueBulletin($this->bulletinsByDate[$date] ?? []); if ($uuid !== null) { return $uuid; } $timestamp = strtotime($date); if ($timestamp === false) { return null; } for ($delta = 1; $delta <= 7; $delta++) { foreach ([$delta, -$delta] as $days) { $tryDate = date('Y-m-d', $timestamp + ($days 86400)); $uuid = $this->pickUniqueBulletin($this->bulletinsByDate[$tryDate] ?? []); if ($uuid !== null) { return $uuid; } } } return null; } /** * @param list<object> $candidates / private function pickUniqueBulletin(array $candidates): ?string { if ($candidates === []) { return null; } if (count($candidates) === 1) { return (string) $candidates[0]->id; } $regular = array_values(array_filter( $candidates, fn ($bulletin) => ! (bool) $bulletin->extraordinaryEdition )); if (count($regular) === 1) { return (string) $regular[0]->id; } return null; } /* * @return array{csv_rows: int, updated: int, skipped: int, unmapped: int} / private function applyMappingToTable(string $table, string $csvFile, string $pkColumn, bool $dryRun): array { $stats = ['csv_rows' => 0, 'updated' => 0, 'skipped' => 0, 'unmapped' => 0]; foreach ($this->readCsvRows($csvFile) as $row) { $legacyId = trim((string) ($row['id_boletim_oficial'] ?? '')); $recordId = trim((string) ($row[$pkColumn] ?? '')); if ($legacyId === '' \|\| $recordId === '') { continue; } $stats['csv_rows']++; $uuid = $this->legacyToUuid[$legacyId] ?? null; if ($uuid === null) { $stats['unmapped']++; continue; } $current = DB::table($table) ->where($pkColumn, $recordId) ->value('id_boletim_oficial'); if ($current !== null && trim((string) $current) === $uuid) { $stats['skipped']++; continue; } if (! $dryRun) { DB::table($table) ->where($pkColumn, $recordId) ->update(['id_boletim_oficial' => $uuid]); } $stats['updated']++; } return $stats; } /* * @return \Generator<string, array<string, string>> */ private function readCsvRows(string $filename): \Generator { $path = LegislacaoImportPaths::resolveCsvPath($this->basePath, $filename); $handle = fopen($path, 'r'); if ($handle === false) { throw new \RuntimeException("Nao foi possivel abrir: {$path}"); } try { $header = fgetcsv($handle, 0, ';'); if (! $header) { return; } $header = array_map(fn ($value) => $this->normalizeHeader((string) $value), $header); while (($row = fgetcsv($handle, 0, ';')) !== false) { if ($this->isRowEmpty($row)) { continue; } $mapped = []; foreach ($header as $index => $column) { $mapped[$column] = $this->decode((string) ($row[$index] ?? '')); } yield $mapped; } } finally { fclose($handle); } } private function normalizeHeader(string $value): string { return trim(mb_strtolower($this->decode($value))); } private function decode(string $value): string { $value = trim($value); if ($value === '') { return ''; } return mb_convert_encoding($value, 'UTF-8', 'UTF-8, ISO-8859-1, Windows-1252'); } private function isRowEmpty(array $row): bool { foreach ($row as $value) { if (trim((string) $value) !== '') { return false; } } return true; } }

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