File manager

File manager - Edit - /var/www/html/portalHomolog/app/Console/Commands/ImportAtasCommand.php

Back
<?php namespace App\Console\Commands; use App\Models\Ata; use App\Models\Structure; use Illuminate\Console\Command; use Illuminate\Support\Facades\DB; class ImportAtasCommand extends Command { private const SIGLA_MAPPINGS = [ 'SE' => 'SEJIN', 'SAD' => 'SMGP', 'SDUS' => 'SUPJ', 'SEPDC' => 'SPDC', 'SGRI' => 'SAG', 'APREV' => 'ANGRAPREV', 'SEC' => 'SEJIN', 'SESL' => 'SEL', 'SPP' => 'SPG', 'SIOP.SEPDC' => 'SPDC', 'SDUS.SEPJ' => 'SUPJ', 'SDUS.SESEP' => 'SSP', 'SDSP.SEESL' => 'SEL', 'SDSP.SEASS' => 'SDSP', 'SE.SEGED' => 'SEJIN', 'SIOP' => 'SOH', 'SGRI.SESP' => 'SAG', 'SGRI.SEPGE' => 'SPG', 'SDUS.SEPDC' => 'SPDC', 'SEESL' => 'SEL', 'SESL.SEEC' => 'SEL', 'IMP' => 'SAG', 'SDE.SEAAP' => 'SAAP', ]; protected $signature = 'atas:import {--file= : Caminho absoluto/relativo do CSV}'; protected $description = 'Importa atas a partir de arquivo CSV legado.'; public function handle(): int { set_time_limit(0); ini_set('max_execution_time', '0'); $fileOption = $this->option('file'); $csvPath = $fileOption ? $this->resolveCsvPath((string) $fileOption) : base_path('temp' . DIRECTORY_SEPARATOR . 'importacao_atas' . DIRECTORY_SEPARATOR . 'PMAR_ATAS_EXPORTADAS-30032026.csv'); if (!is_file($csvPath)) { $this->error("Arquivo CSV nao encontrado: {$csvPath}"); return self::FAILURE; } $handle = fopen($csvPath, 'r'); if ($handle === false) { $this->error("Nao foi possivel abrir o arquivo: {$csvPath}"); return self::FAILURE; } $header = fgetcsv($handle, 0, ';'); if (!$header) { fclose($handle); $this->error('CSV vazio ou cabecalho invalido.'); return self::FAILURE; } $header = array_map(fn ($col) => $this->normalizeHeader($col), $header); $required = [ 'nr_ata', 'ds_objeto', 'nr_processo', 'ds_link_bo', 'nr_bo', 'ds_sigla', 'dt_data_ata', 'dt_vigencia_ata', 'dt_bo', 'nm_arquivo_ata', ]; foreach ($required as $column) { if (!in_array($column, $header, true)) { fclose($handle); $this->error("Cabecalho obrigatorio ausente no CSV: {$column}"); return self::FAILURE; } } $this->info('Carregando indices em memoria (atas, secretarias, boletins)...'); $existingPairs = $this->loadExistingAtaPairs(); [$boletimByNumberDate, $boletimLatestByNumber] = $this->loadBoletimLookups(); $secretariaBySigla = $this->loadSecretariaByAbbreviation(); $stats = ['imported' => 0, 'skipped' => 0, 'errors' => 0]; $unknownSiglas = []; $line = 1; $batch = []; $batchSize = 250; $now = now()->format('Y-m-d H:i:s'); while (($row = fgetcsv($handle, 0, ';')) !== false) { $line++; if ($this->isRowEmpty($row)) { $stats['skipped']++; continue; } try { $data = $this->mapRow($header, $row); $nrAta = $this->limit($data['nr_ata'] ?? '', 50); $nrProcesso = $this->limit($data['nr_processo'] ?? '', 50); if ($nrAta === '' \|\| $nrProcesso === '') { $stats['errors']++; continue; } $pairKey = $nrAta . "\0" . $nrProcesso; if (isset($existingPairs[$pairKey])) { $stats['skipped']++; continue; } $sigla = $this->normalizeSigla($data['ds_sigla'] ?? ''); $secretariaId = $secretariaBySigla[mb_strtoupper($sigla)] ?? null; if ($secretariaId === null) { $stats['errors']++; if (!isset($unknownSiglas[$sigla])) { $unknownSiglas[$sigla] = 0; } $unknownSiglas[$sigla]++; continue; } $nrBo = $this->limit($data['nr_bo'] ?? '', 50); $dtBo = $this->normalizeDate($data['dt_bo'] ?? null); $idBo = $this->resolveBoletimId($nrBo, $dtBo, $boletimByNumberDate, $boletimLatestByNumber); $batch[] = [ 'nr_ata' => $nrAta, 'ds_objeto' => $this->limit($data['ds_objeto'] ?? '', 100), 'id_secretaria' => (string) $secretariaId, 'nr_processo' => $nrProcesso, 'ds_link_bo' => $this->limit($data['ds_link_bo'] ?? '', 250), 'nr_bo' => $nrBo, 'id_bo' => $idBo, 'ds_sigla' => $sigla, 'id_secretaria_ata' => (string) $secretariaId, 'dt_data_ata' => $this->normalizeDate($data['dt_data_ata'] ?? null) ?? '', 'dt_vigencia_ata' => $this->normalizeDate($data['dt_vigencia_ata'] ?? null) ?? '', 'dt_bo' => $dtBo, 'nm_arquivo_ata' => $this->normalizeArquivo($data['nm_arquivo_ata'] ?? ''), 'created_at' => $now, 'updated_at' => $now, ]; $existingPairs[$pairKey] = true; $stats['imported']++; if (count($batch) >= $batchSize) { DB::table('atas')->insert($batch); $batch = []; } } catch (\Throwable $e) { $stats['errors']++; $this->warn("Linha {$line}: erro ao importar ({$e->getMessage()})."); } } if ($batch !== []) { DB::table('atas')->insert($batch); } fclose($handle); $this->newLine(); $this->info('Importacao de atas concluida.'); $this->table( ['Importados', 'Ignorados', 'Erros'], [[$stats['imported'], $stats['skipped'], $stats['errors']]] ); if (!empty($unknownSiglas)) { arsort($unknownSiglas); $this->newLine(); $this->warn('Siglas de secretaria nao encontradas (top 20):'); $rows = []; $i = 0; foreach ($unknownSiglas as $sigla => $count) { $rows[] = [$sigla === '' ? '(vazia)' : $sigla, $count]; $i++; if ($i >= 20) { break; } } $this->table(['Sigla', 'Ocorrencias'], $rows); } return self::SUCCESS; } private function resolveCsvPath(string $path): string { if (str_starts_with($path, DIRECTORY_SEPARATOR) \|\| preg_match('/^[A-Za-z]:[\/\\\\]/', $path)) { return $path; } return base_path($path); } private function mapRow(array $header, array $row): array { $mapped = []; foreach ($header as $i => $key) { $mapped[$key] = $this->decode((string) ($row[$i] ?? '')); } return $mapped; } private function normalizeHeader(string $header): string { return trim(mb_strtolower($this->decode($header))); } 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 normalizeDate(?string $value): ?string { $value = trim((string) $value); if ($value === '') { return null; } if (preg_match('/^\d{2}\/\d{2}\/\d{4}$/', $value)) { [$d, $m, $y] = explode('/', $value); return "{$y}-{$m}-{$d}"; } if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $value)) { return $value; } return null; } /** * @return array<string, true> / private function loadExistingAtaPairs(): array { $pairs = []; foreach (Ata::query()->select('nr_ata', 'nr_processo')->cursor() as $row) { $pairs[$row->nr_ata . "\0" . $row->nr_processo] = true; } return $pairs; } /* * Mesma regra de prioridade do Eloquent: maior status vence. * * @return array<string, string> abbreviation UPPER => structure id (uuid) / private function loadSecretariaByAbbreviation(): array { $map = []; $rows = Structure::query() ->orderByDesc('status') ->select('id', 'abbreviation') ->cursor(); foreach ($rows as $row) { $abbr = mb_strtoupper(trim((string) $row->abbreviation)); if ($abbr !== '' && !isset($map[$abbr])) { $map[$abbr] = (string) $row->id; } } return $map; } /* * @return array{0: array<string, string>, 1: array<int, string>} */ private function loadBoletimLookups(): array { $byNumberDate = []; $latestByNumber = []; $cursor = DB::connection('mysql') ->table('bulletins') ->orderByDesc('publicationDate') ->select('id', 'number', 'publicationDate') ->cursor(); foreach ($cursor as $b) { $num = (int) $b->number; $dateStr = ''; if (!empty($b->publicationDate)) { $dateStr = substr((string) $b->publicationDate, 0, 10); } $key = $num . '\|' . $dateStr; if ($dateStr !== '' && !isset($byNumberDate[$key])) { $byNumberDate[$key] = (string) $b->id; } if (!isset($latestByNumber[$num])) { $latestByNumber[$num] = (string) $b->id; } } return [$byNumberDate, $latestByNumber]; } private function resolveBoletimId( string $nrBo, ?string $dtBo, array $byNumberDate, array $latestByNumber ): ?string { if ($nrBo === '') { return null; } $num = (int) $nrBo; if ($num === 0) { return null; } if (!empty($dtBo)) { $key = $num . '\|' . $dtBo; if (isset($byNumberDate[$key])) { return $byNumberDate[$key]; } } return $latestByNumber[$num] ?? null; } private function normalizeSigla(string $sigla): string { $sigla = mb_strtoupper(trim($sigla)); if ($sigla === '') { return ''; } if (isset(self::SIGLA_MAPPINGS[$sigla])) { return self::SIGLA_MAPPINGS[$sigla]; } return $this->limit($sigla, 10); } private function normalizeArquivo(string $arquivo): string { $arquivo = trim($arquivo); if ($arquivo === '') { return ''; } // Para arquivos legados com apenas o nome (ata-...pdf), monta URL publica. if (str_starts_with($arquivo, 'http://') \|\| str_starts_with($arquivo, 'https://')) { return $this->limit($arquivo, 255); } if (str_starts_with($arquivo, 'storage/') \|\| str_starts_with($arquivo, 'atas/')) { return $this->limit($arquivo, 255); } $arquivo = ltrim($arquivo, '/'); if (str_starts_with($arquivo, 'angra.rj.gov.br/')) { return $this->limit('https://' . $arquivo, 255); } return $this->limit('https://angra.rj.gov.br/downloads/ata/' . $arquivo, 255); } private function limit(string $value, int $max): string { return mb_substr(trim($value), 0, $max); } 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.02 | proxy | phpinfo | Settings