File manager

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

Back
<?php namespace Database\Seeders; use App\Models\IncentivoCultural; use Illuminate\Database\Seeder; use Illuminate\Support\Facades\DB; use PhpOffice\PhpSpreadsheet\IOFactory; class ImportIncentivosCulturaisSeeder extends Seeder { private const PLANILHA_RELATIVE_PATH = 'temp/incentivos-culturais/Cultura e Esposte.xlsx'; private const DATA_START_ROW = 3; /** Evita loop até o fim da grade quando a planilha vem com última linha “falsa” (ex.: 1048576). */ private const MAX_SCAN_ROW = 50000; private const EMPTY_ROWS_TO_STOP = 2; public function run(): void { $planilhaPath = base_path(self::PLANILHA_RELATIVE_PATH); if (! is_file($planilhaPath)) { $this->command?->error('Arquivo não encontrado: '.self::PLANILHA_RELATIVE_PATH); return; } ini_set('memory_limit', '512M'); DB::disableQueryLog(); $this->command?->info('Importando Incentivos Culturais...'); $spreadsheet = IOFactory::load($planilhaPath); $sheet = $spreadsheet->getActiveSheet(); IncentivoCultural::query()->delete(); $agora = now(); $batch = []; $importados = 0; $emptyStreak = 0; for ($r = self::DATA_START_ROW; $r <= self::MAX_SCAN_ROW; $r++) { $proponente = $this->cellString($sheet, 'A', $r); $projeto = $this->cellString($sheet, 'B', $r); $valorRaw = $this->cellString($sheet, 'C', $r); $objeto = $this->cellString($sheet, 'D', $r); $exercicio = $this->parseExercicio($sheet, 'E', $r); if ($proponente === '' && $projeto === '') { $emptyStreak++; if ($emptyStreak >= self::EMPTY_ROWS_TO_STOP) { break; } continue; } $emptyStreak = 0; if ($proponente === '' \|\| $projeto === '' \|\| $exercicio === null) { $this->command?->warn("Linha {$r} ignorada (dados incompletos)."); continue; } $batch[] = [ 'proponente' => $proponente, 'projeto' => $projeto, 'valor_projeto_raw' => $valorRaw !== '' ? $valorRaw : null, 'valor_projeto' => $this->parseMoney($valorRaw), 'objeto' => $objeto !== '' ? $objeto : '-', 'exercicio' => $exercicio, 'created_at' => $agora, 'updated_at' => $agora, ]; $importados++; if (count($batch) >= 200) { DB::table('incentivos_culturais')->insert($batch); $batch = []; } } if ($batch !== []) { DB::table('incentivos_culturais')->insert($batch); } $this->command?->info("Concluído: {$importados} registro(s). Fonte: ".self::PLANILHA_RELATIVE_PATH); } private function cellString($sheet, string $col, int $row): string { $cell = $sheet->getCell($col.$row); $v = $cell->getFormattedValue(); if ($v === null) { return ''; } if (is_numeric($v)) { return trim((string) $cell->getCalculatedValue()); } return trim((string) $v); } private function parseExercicio($sheet, string $col, int $row): ?int { $cell = $sheet->getCell($col.$row); $v = $cell->getCalculatedValue(); if ($v === null \|\| $v === '') { return null; } if (is_numeric($v)) { $n = (int) $v; return $n > 0 ? $n : null; } $digits = preg_replace('/\D/', '', (string) $v); return $digits !== '' ? (int) $digits : null; } private function parseMoney(string $raw): ?float { if ($raw === '') { return null; } $s = preg_replace('/[^\d.,]/', '', $raw); if ($s === '' \|\| $s === null) { return null; } if (str_contains($s, ',') && str_contains($s, '.')) { $lastComma = strrpos($s, ','); $lastDot = strrpos($s, '.'); if ($lastComma > $lastDot) { $s = str_replace('.', '', $s); $s = str_replace(',', '.', $s); } else { $s = str_replace(',', '', $s); } } elseif (str_contains($s, ',')) { $parts = explode(',', $s); if (count($parts) === 2 && strlen($parts[1]) <= 2) { $s = str_replace('.', '', $parts[0]).'.'.$parts[1]; } else { $s = str_replace(',', '', $s); } } return is_numeric($s) ? (float) $s : null; } }

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