File manager

File manager - Edit - /var/www/html/transparencia/database/seeders/ImportDividaAtivaSeeder.php

Back
<?php namespace Database\Seeders; use Illuminate\Database\Seeder; use Illuminate\Support\Carbon; use Illuminate\Support\Facades\DB; use Illuminate\Support\Str; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; use PhpOffice\PhpSpreadsheet\Reader\IReadFilter; use Symfony\Component\Console\Helper\ProgressBar; class ImportDividaAtivaSeeder extends Seeder { private const PLANILHA_RELATIVE_PATH = 'temp/Atualização da Divida Ativa-atualizada(ultimos-5-anos).csv'; private const CHUNK_SIZE = 1000; private const DB_INSERT_CHUNK_SIZE = 200; public function run(): void { $planilhaPath = $this->resolvePlanilhaPath(); // Paliativo: evita estouro por limite baixo do PHP CLI. ini_set('memory_limit', '1024M'); gc_enable(); // Muito importante para seeds grandes: evita acumular queries na memória. DB::disableQueryLog(); $connection = DB::connection('mysql_divida_ativa'); $connection->disableQueryLog(); $this->command?->info('Importando dados da Dívida Ativa...'); try { $connection->table('divida_ativa')->delete(); $connection->table('arquivos')->delete(); $connection->table('registros')->delete(); $headerMap = $this->readHeaderMap($planilhaPath); $totalDataRows = $this->getTotalDataRows($planilhaPath); $processedRows = 0; $importados = 0; $erros = 0; $startRow = 2; $progressBar = $this->createProgressBar($totalDataRows); $agora = now(); $this->command?->line('Fonte: ' . self::PLANILHA_RELATIVE_PATH . ' (últimos 5 anos — priorizando CSV para baixo consumo de memória).'); // Cria um único "arquivo" para toda a importação (evita milhares de inserts). $arquivoId = (string) Str::uuid(); $connection->table('arquivos')->insert([ 'id' => $arquivoId, 'created_at' => $agora, 'updated_at' => $agora, 'owner_id' => null, 'tipo' => 'ARQUIVO', 'nome' => 'Dívida Ativa — últimos 5 anos (planilha filtrada)', 'link' => self::PLANILHA_RELATIVE_PATH, 'status' => 'ATIVADO', ]); while (true) { $endRow = $startRow + self::CHUNK_SIZE - 1; $rows = $this->readRowsChunk($planilhaPath, $startRow, $endRow); if ($rows === []) { break; } $registrosBatch = []; $dividasBatch = []; foreach ($rows as $values) { if ($this->isEmptyRow($values)) { continue; } $documento = $this->digitsOnly($this->valueByHeader($values, $headerMap, 'cpfcnpj')); $cpf = strlen($documento) === 11 ? $documento : null; $cnpj = strlen($documento) === 14 ? $documento : null; if ($cpf === null && $cnpj === null) { if ($documento !== '') { $erros++; } continue; } $registroId = (string) Str::uuid(); $registrosBatch[] = [ 'id' => $registroId, 'created_at' => $agora, 'updated_at' => $agora, 'owner_id' => null, 'numInscricao' => $this->valueByHeader($values, $headerMap, 'inscricao'), 'numImovel' => $this->valueByHeader($values, $headerMap, 'imovel'), 'cpf' => $cpf, 'cnpj' => $cnpj, 'nome' => $this->valueByHeader($values, $headerMap, 'nome'), 'origemDivida' => $this->valueByHeader($values, $headerMap, 'origem'), 'exercicio' => $this->toInt($this->valueByHeader($values, $headerMap, 'exercicio')), 'dividaData' => $this->parseDate($this->valueByHeader($values, $headerMap, 'datadividaativa')), 'numCertidao' => $this->valueByHeader($values, $headerMap, 'certidao'), 'dividaStatus' => $this->valueByHeader($values, $headerMap, 'status'), 'dividaParcelado' => $this->toBoolean($this->valueByHeader($values, $headerMap, 'parcelado')), 'dividaProtestado' => $this->toBoolean($this->valueByHeader($values, $headerMap, 'protestado')), 'dividaAjuizado' => $this->toBoolean($this->valueByHeader($values, $headerMap, 'ajuizado')), 'isPJ' => $cnpj !== null ? 1 : 0, 'dividaValorTotal' => $this->toMoney($this->valueByHeader($values, $headerMap, 'valortotal')), 'informacoes' => null, 'status' => 'ATIVADO', ]; $dividasBatch[] = [ 'id' => (string) Str::uuid(), 'created_at' => $agora, 'updated_at' => $agora, 'owner_id' => null, 'user_id' => null, 'empresa_id' => null, 'divida_ativa_registro_id' => $registroId, 'divida_ativa_arquivo_id' => $arquivoId, 'status' => 'ATIVADO', ]; $importados++; } if ($registrosBatch !== []) { $connection->beginTransaction(); try { $this->insertInChunks($connection, 'registros', $registrosBatch); $this->insertInChunks($connection, 'divida_ativa', $dividasBatch); $connection->commit(); } catch (\Throwable $e) { $connection->rollBack(); $erros += count($registrosBatch); throw $e; } } $processedRows += count($rows); if ($progressBar) { $progressBar->setProgress(min($processedRows, $totalDataRows)); $progressBar->display(); } $this->logProgressPercent($processedRows, $totalDataRows, $importados); $startRow += self::CHUNK_SIZE; unset($rows, $registrosBatch, $dividasBatch); gc_collect_cycles(); } if ($progressBar) { $progressBar->finish(); $this->command?->newLine(); } $this->command?->newLine(); $this->command?->info('--- Resumo da importação ---'); $this->command?->info("Importadas: {$importados}"); $this->command?->info("Erros (documento inválido ou falha ao gravar lote): {$erros}"); } catch (\Throwable $e) { $this->command?->error('Erro ao importar Dívida Ativa: ' . $e->getMessage()); throw $e; } } private function buildHeaderMap(array $headerRow): array { $map = []; foreach ($headerRow as $index => $value) { $normalized = mb_strtolower(trim((string) $value)); $normalized = preg_replace('/[^a-z0-9]/', '', $normalized ?? ''); if ($normalized !== '') { $map[$normalized] = $index; } } return $map; } private function readHeaderMap(string $planilhaPath): array { $reader = IOFactory::createReaderForFile($planilhaPath); $reader->setReadDataOnly(true); $reader->setReadEmptyCells(false); $reader->setReadFilter(new DividaAtivaChunkReadFilter(1, 1)); $spreadsheet = $reader->load($planilhaPath); $worksheet = $spreadsheet->getActiveSheet(); $headerRow = []; foreach ($worksheet->getRowIterator(1, 1) as $row) { $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(true); foreach ($cellIterator as $cell) { $columnIndex = Coordinate::columnIndexFromString($cell->getColumn()) - 1; $headerRow[$columnIndex] = trim((string) $cell->getValue()); } } $spreadsheet->disconnectWorksheets(); unset($spreadsheet); return $this->buildHeaderMap($headerRow); } private function readRowsChunk(string $planilhaPath, int $startRow, int $endRow): array { $reader = IOFactory::createReaderForFile($planilhaPath); $reader->setReadDataOnly(true); $reader->setReadEmptyCells(false); $reader->setReadFilter(new DividaAtivaChunkReadFilter($startRow, $endRow)); $spreadsheet = $reader->load($planilhaPath); $worksheet = $spreadsheet->getActiveSheet(); $rows = []; foreach ($worksheet->getRowIterator($startRow, $endRow) as $row) { $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(true); $values = []; foreach ($cellIterator as $cell) { $columnIndex = Coordinate::columnIndexFromString($cell->getColumn()) - 1; $values[$columnIndex] = trim((string) $cell->getValue()); } $rows[] = $values; } $spreadsheet->disconnectWorksheets(); unset($spreadsheet); return $rows; } private function getTotalDataRows(string $planilhaPath): int { $reader = IOFactory::createReaderForFile($planilhaPath); $worksheetInfo = $reader->listWorksheetInfo($planilhaPath); $totalRows = (int) ($worksheetInfo[0]['totalRows'] ?? 1); return max(1, $totalRows - 1); } private function createProgressBar(int $totalDataRows): ?ProgressBar { if (!$this->command) { return null; } ProgressBar::setFormatDefinition( 'divida_ativa_import', ' %current%/%max% \|%bar%\| %percent:3s%% concluído' ); $progressBar = $this->command->getOutput()->createProgressBar($totalDataRows); $progressBar->setFormat('divida_ativa_import'); $progressBar->setBarWidth(36); $progressBar->setBarCharacter('='); $progressBar->setEmptyBarCharacter('-'); $progressBar->setProgressCharacter('>'); $progressBar->setRedrawFrequency(1); $progressBar->minSecondsBetweenRedraws(0.0); $progressBar->start(); return $progressBar; } /** * Linha explícita com % (Git Bash / Windows às vezes não redesenham a barra durante leitura pesada). / private function logProgressPercent(int $processedRows, int $totalDataRows, int $importados): void { if (!$this->command \|\| $totalDataRows <= 0) { return; } $atual = min($processedRows, $totalDataRows); $percent = min(100.0, (100.0 $atual) / $totalDataRows); $barra = $this->asciiPercentBar($percent, 32); $this->command->line(sprintf( ' %s %.1f%% da planilha (%s / %s linhas lidas) — registros importados: %s', $barra, $percent, number_format($atual, 0, ',', '.'), number_format($totalDataRows, 0, ',', '.'), number_format($importados, 0, ',', '.') )); } private function asciiPercentBar(float $percent, int $width = 32): string { $pct = max(0.0, min(100.0, $percent)); $filled = (int) round($width * $pct / 100.0); $filled = max(0, min($width, $filled)); return '[' . str_repeat('=', $filled) . str_repeat('-', $width - $filled) . ']'; } private function insertInChunks($connection, string $table, array $rows): void { foreach (array_chunk($rows, self::DB_INSERT_CHUNK_SIZE) as $chunk) { $connection->table($table)->insert($chunk); } } private function valueByHeader(array $row, array $headerMap, string $header): ?string { $index = $headerMap[$header] ?? null; if ($index === null) { return null; } $value = trim((string) ($row[$index] ?? '')); return $value !== '' ? $value : null; } private function isEmptyRow(array $row): bool { foreach ($row as $value) { if (trim((string) $value) !== '') { return false; } } return true; } private function digitsOnly(?string $value): string { return preg_replace('/\D+/', '', (string) $value) ?? ''; } private function toBoolean(?string $value): bool { return mb_strtolower(trim((string) $value)) === 'sim'; } private function toMoney(?string $value): float { $normalized = str_replace(['R$', '.', ' '], '', (string) $value); $normalized = str_replace(',', '.', $normalized); return is_numeric($normalized) ? (float) $normalized : 0.0; } private function toInt(?string $value): ?int { if ($value === null \|\| !is_numeric($value)) { return null; } return (int) $value; } private function parseDate(?string $value): ?string { if ($value === null) { return null; } try { $date = Carbon::createFromFormat('d/m/Y', trim($value)); return $date ? $date->format('Y-m-d') : null; } catch (\Throwable $e) { return null; } } private function resolvePlanilhaPath(): string { $candidates = [ self::PLANILHA_RELATIVE_PATH, 'temp/Atualizacao da Divida Ativa-atualizada(ultimos-5-anos).csv', 'temp/Atualização da Divida Ativa-atualizada(ultimos-5-anos).xlsx', 'temp/Atualizacao da Divida Ativa-atualizada(ultimos-5-anos).xlsx', ]; foreach ($candidates as $relative) { $full = base_path($relative); if (is_file($full)) { return $full; } } throw new \RuntimeException( 'Planilha não encontrada. Coloque o arquivo em: ' . base_path(self::PLANILHA_RELATIVE_PATH) ); } } class DividaAtivaChunkReadFilter implements IReadFilter { public function __construct( private readonly int $startRow, private readonly int $endRow ) {} public function readCell($columnAddress, $row, $worksheetName = ''): bool { return $row >= $this->startRow && $row <= $this->endRow; } }

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