File manager

File manager - Edit - /var/www/html/portal/app/Http/Services/LicitacaoProcedimentoImportService.php

Back
<?php namespace App\Http\Services; use App\Models\LicitacoesProcedimentos\Processo; use App\Models\LicitacoesProcedimentos\ProcessoUpload; use Carbon\Carbon; use Illuminate\Support\Facades\DB; use Illuminate\Support\Facades\Log; use Illuminate\Support\Facades\Schema; class LicitacaoProcedimentoImportService { private const CHUNK_SIZE = 500; private const MAPS_FILE = 'import-licitacoes-maps.json'; private int $imported = 0; private int $skipped = 0; private int $errors = 0; /** @var array<int, array<string, mixed>> / private array $issues = []; private string $currentEtapa = ''; private string $currentFile = ''; /* @var array<string, array<int, int>> / private array $idMaps = ['licitacao' => [], 'procedimento' => []]; private ?\Closure $onProgress = null; public function setProgressCallback(\Closure $callback): void { $this->onProgress = $callback; } /* * @return array{imported: int, skipped: int, errors: int} / public function getStats(): array { return [ 'imported' => $this->imported, 'skipped' => $this->skipped, 'errors' => $this->errors, ]; } public function resetStats(): void { $this->imported = 0; $this->skipped = 0; $this->errors = 0; } public function resetIssues(): void { $this->issues = []; } /* * @return array<int, array<string, mixed>> / public function getIssues(): array { return $this->issues; } /* * Limpa dados importados para permitir reexecucao idempotente. * * @param 'uploads'\|'licitacoes'\|null $only null/completo ou licitacoes: processos + uploads; uploads: so uploads * @return array{uploads: int, processos: int} / public function cleanImportedData(?string $only = null): array { $uploadsCount = DB::table('uploads_licitacoes_procedimentos')->count(); $processosCount = DB::table('licitacoes_procedimentos')->count(); DB::statement('SET FOREIGN_KEY_CHECKS=0'); try { if ($only === 'uploads') { DB::table('uploads_licitacoes_procedimentos')->truncate(); } else { $this->nullifyOptionalForeignKeys(); DB::table('uploads_licitacoes_procedimentos')->truncate(); DB::table('licitacoes_procedimentos')->truncate(); $this->idMaps = ['licitacao' => [], 'procedimento' => []]; $this->removeMapsFile(); } } finally { DB::statement('SET FOREIGN_KEY_CHECKS=1'); } $removed = [ 'uploads' => $uploadsCount, 'processos' => $only === 'uploads' ? 0 : $processosCount, ]; $this->log('info', 'Limpeza pre-importacao concluida (idempotencia).', [ 'only' => $only ?? 'completo', 'uploads_removidos' => $removed['uploads'], 'processos_removidos' => $removed['processos'], ]); return $removed; } /* * @return array<string, array{imported: int, skipped: int, errors: int}> / public function importAll(string $basePath): array { $this->resetIssues(); $results = []; $results['licitacoes'] = $this->importLicitacoes($basePath); $results['procedimentos'] = $this->importProcedimentos($basePath); $this->saveMaps(); $results['uploads_licitacoes'] = $this->importUploadsLicitacoes($basePath); $results['uploads_procedimentos'] = $this->importUploadsProcedimentos($basePath); return $results; } /* * Import only main records (licitacoes + procedimentos). * * @return array<string, array{imported: int, skipped: int, errors: int}> / public function importOnlyLicitacoes(string $basePath): array { $this->resetIssues(); $results = []; $results['licitacoes'] = $this->importLicitacoes($basePath); $results['procedimentos'] = $this->importProcedimentos($basePath); $this->saveMaps(); return $results; } /* * Import only uploads (requires maps from a previous main-records import). * * @return array<string, array{imported: int, skipped: int, errors: int}> / public function importOnlyUploads(string $basePath): array { $this->resetIssues(); $this->loadMaps(); if (empty($this->idMaps['licitacao']) && empty($this->idMaps['procedimento'])) { throw new \RuntimeException( 'Nenhum mapa de IDs encontrado. Execute a importacao de licitacoes primeiro ou execute a importacao completa.' ); } $results = []; $results['uploads_licitacoes'] = $this->importUploadsLicitacoes($basePath); $results['uploads_procedimentos'] = $this->importUploadsProcedimentos($basePath); return $results; } /* * Count data lines (excluding header) in a semicolon-delimited CSV. / public function countCsvLines(string $filePath): int { if (!file_exists($filePath)) { return 0; } $handle = fopen($filePath, 'r'); if ($handle === false) { return 0; } fgetcsv($handle, 0, ';', '"', ''); $count = 0; while (fgetcsv($handle, 0, ';', '"', '') !== false) { $count++; } fclose($handle); return $count; } // ────────────────────────────────────────────── // Individual import orchestrators // ────────────────────────────────────────────── /* * @return array{imported: int, skipped: int, errors: int} / private function importLicitacoes(string $basePath): array { $this->resetStats(); $this->currentEtapa = 'licitacoes'; $file = $basePath . DIRECTORY_SEPARATOR . 'TB_PMAR_LICITACAO-29052026.csv'; $this->currentFile = $file; $this->log('info', "Iniciando importacao de licitacoes: {$file}"); $this->importProcessosFromCsv($file, 'licitacao'); $stats = $this->getStats(); $this->log('info', 'Importacao de licitacoes finalizada.', $stats); return $stats; } /* * @return array{imported: int, skipped: int, errors: int} / private function importProcedimentos(string $basePath): array { $this->resetStats(); $this->currentEtapa = 'procedimentos'; $file = $basePath . DIRECTORY_SEPARATOR . 'TB_PMAR_PROCEDIMENTOS-29052026.csv'; $this->currentFile = $file; $this->log('info', "Iniciando importacao de procedimentos: {$file}"); $this->importProcessosFromCsv($file, 'procedimento'); $stats = $this->getStats(); $this->log('info', 'Importacao de procedimentos finalizada.', $stats); return $stats; } /* * @return array{imported: int, skipped: int, errors: int} / private function importUploadsLicitacoes(string $basePath): array { $this->resetStats(); $this->currentEtapa = 'uploads_licitacoes'; $file = $basePath . DIRECTORY_SEPARATOR . 'TB_PMAR_LICITACAO-UPLOAD-29052026.csv'; $this->currentFile = $file; $this->log('info', "Iniciando importacao de uploads de licitacoes: {$file}"); $this->importUploadsFromCsv($file, 'licitacao'); $stats = $this->getStats(); $this->log('info', 'Importacao de uploads de licitacoes finalizada.', $stats); return $stats; } /* * @return array{imported: int, skipped: int, errors: int} / private function importUploadsProcedimentos(string $basePath): array { $this->resetStats(); $this->currentEtapa = 'uploads_procedimentos'; $file = $basePath . DIRECTORY_SEPARATOR . 'TB_PMAR_PROCEDIMENTO_UPLOAD-29052026.csv'; $this->currentFile = $file; $this->log('info', "Iniciando importacao de uploads de procedimentos: {$file}"); $this->importUploadsFromCsv($file, 'procedimento'); $stats = $this->getStats(); $this->log('info', 'Importacao de uploads de procedimentos finalizada.', $stats); return $stats; } // ────────────────────────────────────────────── // Core CSV → DB importers // ────────────────────────────────────────────── private function importProcessosFromCsv(string $filePath, string $tipoProcesso): void { $this->assertFileReadable($filePath); $handle = $this->openCsvHandle($filePath); $headers = $this->readHeaders($handle, false); if (!$headers) { fclose($handle); throw new \RuntimeException("Nao foi possivel ler os headers de: {$filePath}"); } $isLicitacao = ($tipoProcesso === 'licitacao'); $oldIdCol = $isLicitacao ? 'cd_licitacao' : 'cd_procedimento'; $nrCol = $isLicitacao ? 'nr_licitacao' : 'nr_procedimento'; $chunk = []; $lineNumber = 1; while (($row = fgetcsv($handle, 0, ';', '"', '')) !== false) { $lineNumber++; if ($this->isEmptyRow($row)) { continue; } try { $data = $this->zipHeadersRow($headers, $row); if ($data === null) { $this->skipped++; $mensagem = "Linha {$lineNumber}: quantidade de colunas incompativel, ignorada."; $this->recordIssue('ignorado', $mensagem, $lineNumber, null, [ 'expected' => count($headers), 'got' => count($row), ]); continue; } $oldId = $this->toInt($data[$oldIdCol] ?? null); if ($oldId === null) { $this->skipped++; $mensagem = "Linha {$lineNumber}: {$oldIdCol} ausente ou invalido, ignorada."; $this->recordIssue('ignorado', $mensagem, $lineNumber, null); continue; } $record = [ 'tipo_processo' => $tipoProcesso, 'id_setor' => $this->toInt($data['id_setor'] ?? null) ?? 0, 'ds_objeto' => $this->cleanText($data['ds_objeto'] ?? ''), 'nr_processo' => $this->cleanText($data['nr_processo'] ?? null, 30), 'nr_memorando' => $this->cleanText($data['nr_memorando'] ?? null, 30), 'cd_tipo' => $this->toInt($data['cd_tipo'] ?? null) ?? 0, 'nr_licitacao' => $this->cleanText($data[$nrCol] ?? '', 30), 'dt_data' => $this->parseBrDate($data['dt_data'] ?? null), 'dt_hora' => $this->cleanText($data['dt_hora'] ?? null, 5), 'cd_status' => $this->toInt($data['cd_status'] ?? null) ?? 0, 'id_usuario' => $this->cleanText($data['id_usuario'] ?? null, 36), 'created_at' => now(), 'updated_at' => now(), ]; if (!$isLicitacao) { $record['dt_encerramento'] = $this->parseBrDate($data['dt_encerramento'] ?? null); $record['ds_favorecido'] = $this->cleanText($data['ds_favorecido'] ?? null); $record['ds_bo'] = $this->cleanText($data['ds_bo'] ?? null, 50); $record['ds_urlbo'] = $this->cleanText($data['ds_urlbo'] ?? null, 250); $record['boletins'] = $this->buildBoletinsPayload( $record['ds_bo'] ?? null, $record['ds_urlbo'] ?? null ); } $chunk[] = [ 'old_id' => $oldId, 'record' => $record, 'linha' => $lineNumber, 'contexto' => [ 'old_id' => $oldId, 'tipo_processo' => $tipoProcesso, 'nr_licitacao' => $record['nr_licitacao'] ?? null, 'nr_processo' => $record['nr_processo'] ?? null, 'id_setor' => $record['id_setor'] ?? null, ], ]; if (count($chunk) >= self::CHUNK_SIZE) { $this->flushProcessoChunk($chunk, $tipoProcesso); $chunk = []; } } catch (\Throwable $e) { $this->errors++; $mensagem = "Linha {$lineNumber}: {$e->getMessage()}"; $this->recordIssue('erro', $mensagem, $lineNumber, null); } } if (!empty($chunk)) { $this->flushProcessoChunk($chunk, $tipoProcesso); } fclose($handle); } private function flushProcessoChunk(array $chunk, string $tipoProcesso): void { DB::transaction(function () use ($chunk, $tipoProcesso) { foreach ($chunk as $item) { try { $processo = Processo::create($item['record']); $this->idMaps[$tipoProcesso][$item['old_id']] = $processo->id; $this->imported++; $this->notifyProgress(); } catch (\Throwable $e) { $this->errors++; $mensagem = "Falha ao inserir {$tipoProcesso} (old_id={$item['old_id']}): {$e->getMessage()}"; $this->recordIssue( 'erro', $mensagem, $item['linha'] ?? null, "old_id={$item['old_id']}", $item['contexto'] ?? ['old_id' => $item['old_id']], ); } } }); } private function importUploadsFromCsv(string $filePath, string $tipoProcesso): void { $this->assertFileReadable($filePath); $stripHeader = ($tipoProcesso === 'procedimento'); $handle = $this->openCsvHandle($filePath); $headers = $this->readHeaders($handle, $stripHeader); if (!$headers) { fclose($handle); throw new \RuntimeException("Nao foi possivel ler os headers de: {$filePath}"); } $isLicitacao = ($tipoProcesso === 'licitacao'); $fkCol = $isLicitacao ? 'cd_licitacao' : 'cd_procedimento'; $idMap = $this->idMaps[$tipoProcesso] ?? []; $chunk = []; $lineNumber = 1; while (($row = fgetcsv($handle, 0, ';', '"', '')) !== false) { $lineNumber++; if ($this->isEmptyRow($row)) { continue; } try { $data = $this->zipHeadersRow($headers, $row); if ($data === null) { $this->skipped++; $mensagem = "Upload linha {$lineNumber}: colunas incompativeis, ignorada."; $this->recordIssue('ignorado', $mensagem, $lineNumber, null); continue; } $oldProcessoId = $this->toInt($data[$fkCol] ?? null); if ($oldProcessoId === null) { $this->skipped++; $mensagem = "Upload linha {$lineNumber}: {$fkCol} ausente, ignorada."; $this->recordIssue('ignorado', $mensagem, $lineNumber, null); continue; } $newProcessoId = $idMap[$oldProcessoId] ?? null; if ($newProcessoId === null) { $this->skipped++; $mensagem = "Upload linha {$lineNumber}: sem mapeamento para {$fkCol}={$oldProcessoId}, ignorada."; $this->recordIssue( 'ignorado', $mensagem, $lineNumber, "{$fkCol}={$oldProcessoId}", [ 'old_processo_id' => $oldProcessoId, 'id_upload' => $this->toInt($data['id_upload'] ?? null), 'ds_arquivo' => $this->cleanText($data['ds_arquivo'] ?? null, 200), ], ); continue; } $chunk[] = [ 'licitacao_procedimento_id' => $newProcessoId, 'dt_data' => $this->parseIsoDate($data['dt_data'] ?? null), 'ds_arquivo' => $this->buildArquivoPath($data['ds_arquivo'] ?? ''), 'ds_legenda' => $this->cleanText($data['ds_legenda'] ?? null, 100), 'qt_cliques' => $this->toInt($data['qt_cliques'] ?? null) ?? 0, 'created_at' => now(), 'updated_at' => now(), 'linha' => $lineNumber, 'old_processo_id' => $oldProcessoId, ]; if (count($chunk) >= self::CHUNK_SIZE) { $this->flushUploadChunk($chunk); $chunk = []; } } catch (\Throwable $e) { $this->errors++; $mensagem = "Upload linha {$lineNumber}: {$e->getMessage()}"; $this->recordIssue('erro', $mensagem, $lineNumber, null); } } if (!empty($chunk)) { $this->flushUploadChunk($chunk); } fclose($handle); } private function flushUploadChunk(array $chunk): void { DB::transaction(function () use ($chunk) { foreach ($chunk as $record) { $linha = $record['linha'] ?? null; $oldProcessoId = $record['old_processo_id'] ?? null; $persistPayload = array_intersect_key($record, array_flip([ 'licitacao_procedimento_id', 'dt_data', 'ds_arquivo', 'ds_legenda', 'qt_cliques', 'created_at', 'updated_at', ])); try { ProcessoUpload::create($persistPayload); $this->imported++; $this->notifyProgress(); } catch (\Throwable $e) { $this->errors++; $referencia = $oldProcessoId !== null ? "old_processo_id={$oldProcessoId}" : null; $mensagem = "Falha ao inserir upload: {$e->getMessage()}"; $this->recordIssue( 'erro', $mensagem, is_int($linha) ? $linha : null, $referencia, [ 'old_processo_id' => $oldProcessoId, 'ds_arquivo' => $persistPayload['ds_arquivo'] ?? null, ], ); } } }); } // ────────────────────────────────────────────── // CSV helpers // ────────────────────────────────────────────── /* * @return resource / private function openCsvHandle(string $filePath): mixed { $handle = fopen($filePath, 'r'); if ($handle === false) { throw new \RuntimeException("Nao foi possivel abrir: {$filePath}"); } $bom = fread($handle, 3); if ($bom !== "\xEF\xBB\xBF") { rewind($handle); } return $handle; } /* * @return string[]\|null / private function readHeaders(mixed $handle, bool $stripLeadingGarbage): ?array { $headerRow = fgetcsv($handle, 0, ';', '"', ''); if ($headerRow === false \|\| empty($headerRow)) { return null; } $headers = array_map(function (string $h): string { $h = trim($h, " \t\n\r\0\x0B\""); return preg_replace('/^[\x00-\x1F\x7F-\xFF]+/', '', $h); }, $headerRow); if ($stripLeadingGarbage && !empty($headers[0])) { $headers[0] = preg_replace('/^.?(id_)/', '$1', $headers[0]); } return $headers; } /** * @return array<string, string>\|null / private function zipHeadersRow(array $headers, array $row): ?array { $hCount = count($headers); $rCount = count($row); if ($rCount < $hCount) { if ($rCount < (int) ceil($hCount / 2)) { return null; } $row = array_pad($row, $hCount, ''); } if ($rCount > $hCount) { $row = array_slice($row, 0, $hCount); } return array_combine($headers, $row); } private function isEmptyRow(array $row): bool { return count($row) === 1 && ($row[0] === null \|\| trim($row[0]) === ''); } // ────────────────────────────────────────────── // Data sanitisation // ────────────────────────────────────────────── private function toInt(?string $value): ?int { if ($value === null \|\| trim($value) === '') { return null; } $cleaned = preg_replace('/[^0-9\-]/', '', trim($value)); if ($cleaned === '' \|\| $cleaned === '-') { return null; } return (int) $cleaned; } private function parseBrDate(?string $value): ?string { if ($value === null \|\| trim($value) === '') { return null; } $value = trim($value); try { if (preg_match('#^(\d{2})/(\d{2})/(\d{4})\s+(\d{2}:\d{2})#', $value, $m)) { return Carbon::createFromFormat('d/m/Y H:i', "{$m[1]}/{$m[2]}/{$m[3]} {$m[4]}") ->format('Y-m-d H:i:s'); } if (preg_match('#^(\d{2})/(\d{2})/(\d{4})$#', $value)) { return Carbon::createFromFormat('d/m/Y', $value) ->startOfDay() ->format('Y-m-d H:i:s'); } return Carbon::parse($value)->format('Y-m-d H:i:s'); } catch (\Throwable) { $this->log('warning', "Data BR invalida: {$value}"); return null; } } private function parseIsoDate(?string $value): ?string { if ($value === null \|\| trim($value) === '') { return null; } try { return Carbon::parse(trim($value))->format('Y-m-d H:i:s'); } catch (\Throwable) { $this->log('warning', "Data ISO invalida: {$value}"); return null; } } private function cleanText(?string $value, ?int $maxLen = null): ?string { if ($value === null \|\| trim($value) === '') { return null; } $value = trim($value); if (!mb_check_encoding($value, 'UTF-8')) { $converted = @mb_convert_encoding($value, 'UTF-8', 'Windows-1252'); if ($converted !== false) { $value = $converted; } } $value = html_entity_decode($value, ENT_QUOTES \| ENT_HTML5, 'UTF-8'); $value = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]/', '', $value); if ($maxLen !== null && mb_strlen($value) > $maxLen) { $value = mb_substr($value, 0, $maxLen); } return $value === '' ? null : $value; } /* * Build boletins JSON payload from legacy flat columns. * * @return array<int, array{ds_bo: string\|null, ds_urlbo: string\|null}>\|null / private function buildBoletinsPayload(?string $dsBo, ?string $dsUrlbo): ?array { if ($dsBo === null && $dsUrlbo === null) { return null; } return [[ 'ds_bo' => $dsBo, 'ds_urlbo' => $dsUrlbo, ]]; } /* * Prefixa o arquivo vindo do CSV com o caminho público de licitações. / private function buildArquivoPath(?string $rawFilename): ?string { $filename = $this->cleanText($rawFilename, 200); if ($filename === null) { return null; } $normalized = ltrim(str_replace('\\', '/', $filename), '/'); $legacy = 'pmar/assets/img/licitacoes_procedimentos/'; if (str_starts_with($normalized, $legacy)) { $normalized = substr($normalized, strlen($legacy)); } $prefix = LicitacaoProcedimentoService::UPLOAD_PUBLIC_PREFIX . '/'; if (str_starts_with($normalized, $prefix)) { $normalized = substr($normalized, strlen($prefix)); } return $prefix . ltrim($normalized, '/'); } // ────────────────────────────────────────────── // ID-map persistence (for split --only runs) // ────────────────────────────────────────────── private function saveMaps(): void { $path = storage_path('app' . DIRECTORY_SEPARATOR . self::MAPS_FILE); $dir = dirname($path); if (!is_dir($dir)) { mkdir($dir, 0755, true); } $payload = [ 'licitacao' => $this->idMaps['licitacao'], 'procedimento' => $this->idMaps['procedimento'], ]; file_put_contents($path, json_encode($payload, JSON_THROW_ON_ERROR)); $this->log('info', "Mapa de IDs salvo em: {$path}"); } private function removeMapsFile(): void { $path = storage_path('app' . DIRECTORY_SEPARATOR . self::MAPS_FILE); if (file_exists($path)) { unlink($path); } } private function nullifyOptionalForeignKeys(): void { if (Schema::hasTable('atas') && Schema::hasColumn('atas', 'licitacao_procedimento_id')) { DB::table('atas')->whereNotNull('licitacao_procedimento_id')->update(['licitacao_procedimento_id' => null]); } if (Schema::hasTable('contratos') && Schema::hasColumn('contratos', 'licitacao_procedimento_id')) { DB::table('contratos')->whereNotNull('licitacao_procedimento_id')->update(['licitacao_procedimento_id' => null]); } } private function loadMaps(): void { $path = storage_path('app' . DIRECTORY_SEPARATOR . self::MAPS_FILE); if (!file_exists($path)) { throw new \RuntimeException( "Arquivo de mapa de IDs nao encontrado em: {$path}. Execute a importacao de licitacoes primeiro." ); } $data = json_decode(file_get_contents($path), true, 512, JSON_THROW_ON_ERROR); $this->idMaps['licitacao'] = array_map('intval', $data['licitacao'] ?? []); $this->idMaps['procedimento'] = array_map('intval', $data['procedimento'] ?? []); $this->log('info', sprintf( 'Mapa de IDs carregado: %d licitacoes, %d procedimentos', count($this->idMaps['licitacao']), count($this->idMaps['procedimento']) )); } // ────────────────────────────────────────────── // Internal utilities // ────────────────────────────────────────────── private function assertFileReadable(string $filePath): void { if (!file_exists($filePath) \|\| !is_readable($filePath)) { throw new \RuntimeException("Arquivo nao encontrado ou ilegivel: {$filePath}"); } } private function log(string $level, string $message, array $context = []): void { Log::channel('import_licitacoes')->{$level}($message, $context); } /* * @param array<string, mixed> $contexto */ private function recordIssue( string $tipo, string $mensagem, ?int $linha = null, ?string $referencia = null, array $contexto = [], ): void { $this->issues[] = [ 'etapa' => $this->currentEtapa, 'tipo' => $tipo, 'arquivo' => basename($this->currentFile), 'linha' => $linha, 'referencia' => $referencia, 'mensagem' => $mensagem, 'contexto' => $contexto, ]; $this->log($tipo === 'erro' ? 'error' : 'warning', $mensagem, array_filter([ 'etapa' => $this->currentEtapa, 'arquivo' => basename($this->currentFile), 'linha' => $linha, 'referencia' => $referencia, 'contexto' => $contexto !== [] ? $contexto : null, ])); } private function notifyProgress(): void { if ($this->onProgress !== null) { ($this->onProgress)(); } } }

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