File manager

File manager - Edit - /var/www/html/portalHomolog/database/seeders/ContratosImportSeeder.php

Back
<?php namespace Database\Seeders; use App\Models\Contratos\Contrato; use App\Models\Contratos\ContratoArquivo; use App\Models\Contratos\ContratoFavorecido; use Illuminate\Database\Seeder; use Illuminate\Support\Facades\DB; use Illuminate\Support\Str; use PhpOffice\PhpSpreadsheet\IOFactory; /** * Importa a base legada de contratos a partir das planilhas ODS em temp/contratos/. * * Execute: php artisan db:seed --class=ContratosImportSeeder / class ContratosImportSeeder extends Seeder { private const ODS_CONTRATOS = 'temp/contratos/CONTRATOS.ods'; private const ODS_FAVORECIDOS = 'temp/contratos/CONTRATOS_FAVORECIDOS.ods'; private const ODS_ARQUIVOS = 'temp/contratos/CONTRATOS_ARQUIVOS.ods'; private const CSV_SECRETARIAS = 'temp/contratos/Secretarias.csv'; private array $legacyContratoMap = []; /* @var array<string, string\|null> / private array $legacySecretariaMap = []; private array $stats = [ 'favorecidos' => 0, 'contratos' => 0, 'arquivos' => 0, 'arquivos_sem_contrato' => 0, 'secretarias_mapeadas' => 0, 'secretarias_sem_mapeamento' => 0, ]; public function run(): void { $this->command->info('==========================================='); $this->command->info('Importando base de Contratos (ODS)...'); $this->command->info('==========================================='); DB::beginTransaction(); try { $this->validateFiles(); $this->legacySecretariaMap = $this->buildLegacySecretariaMap(); $this->cleanTables(); $this->importFavorecidos(); $this->importContratos(); $this->importArquivos(); DB::commit(); $this->showStats(); } catch (\Throwable $e) { DB::rollBack(); $this->command->error('Falha na importacao: ' . $e->getMessage()); throw $e; } } private function validateFiles(): void { foreach ([self::ODS_CONTRATOS, self::ODS_FAVORECIDOS, self::ODS_ARQUIVOS, self::CSV_SECRETARIAS] as $file) { if (!file_exists(base_path($file))) { throw new \RuntimeException("Arquivo nao encontrado: {$file}"); } } } /* * @return array<string, string\|null> / private function buildLegacySecretariaMap(): array { $this->command->info('Montando depara de secretarias legadas...'); $structuresByAbbreviation = DB::table('structures') ->select(['id', 'abbreviation']) ->get() ->mapWithKeys(fn ($structure) => [ strtoupper(trim((string) $structure->abbreviation)) => (string) $structure->id, ]) ->all(); $overrides = config('contratos_secretarias_mapping.abbreviation_overrides', []); $map = []; $mappedCount = 0; $unmappedIds = []; foreach ($this->readCsv(self::CSV_SECRETARIAS) as $row) { $legacyId = preg_replace('/\D+/', '', (string) ($row['id'] ?? '')); if ($legacyId === '') { continue; } if (array_key_exists($legacyId, $overrides)) { $override = $overrides[$legacyId]; $map[$legacyId] = $override === null ? null : ($structuresByAbbreviation[strtoupper((string) $override)] ?? null); } else { $map[$legacyId] = $this->resolveStructureByAbbreviation( $structuresByAbbreviation, $row['sigla_atual'] ?? null, $row['sigla_antiga'] ?? null ); } if ($map[$legacyId] !== null) { $mappedCount++; } else { $unmappedIds[] = $legacyId; } } $this->command->info("- Secretarias no CSV: " . count($map)); $this->command->info("- Secretarias mapeadas para structures: {$mappedCount}"); if ($unmappedIds !== []) { $this->command->warn('- Secretarias sem structure correspondente: ' . implode(', ', $unmappedIds)); } return $map; } /* * @param array<string, string> $structuresByAbbreviation / private function resolveStructureByAbbreviation( array $structuresByAbbreviation, ?string $siglaAtual, ?string $siglaAntiga ): ?string { foreach ([$siglaAtual, $siglaAntiga] as $sigla) { $sigla = strtoupper(trim((string) $sigla)); if ($sigla === '' \|\| $sigla === 'NULL') { continue; } if (isset($structuresByAbbreviation[$sigla])) { return $structuresByAbbreviation[$sigla]; } } return null; } private function resolveSecretariaUuid(?string $value): ?string { $uuid = $this->nullableUuid($value); if ($uuid !== null) { return $uuid; } $legacyId = preg_replace('/\D+/', '', (string) $value); if ($legacyId === '' \|\| $legacyId === '0') { return null; } if (!array_key_exists($legacyId, $this->legacySecretariaMap)) { $this->stats['secretarias_sem_mapeamento']++; return null; } if ($this->legacySecretariaMap[$legacyId] === null) { $this->stats['secretarias_sem_mapeamento']++; return null; } $this->stats['secretarias_mapeadas']++; return $this->legacySecretariaMap[$legacyId]; } private function cleanTables(): void { $this->command->info('Limpando tabelas de contratos...'); DB::table('contratos_arquivos')->delete(); DB::table('contratos')->delete(); DB::table('contratos_favorecidos')->delete(); } private function importFavorecidos(): void { $this->command->info('Importando favorecidos...'); foreach ($this->readSpreadsheet(self::ODS_FAVORECIDOS) as $row) { $cpfCnpj = $this->sanitizeDigits($row['fav_cpfcnpj'] ?? '', 14); if ($cpfCnpj === null) { continue; } ContratoFavorecido::query()->updateOrCreate( ['fav_cpfcnpj' => $cpfCnpj], ['fav_nome' => $this->nullableString($row['fav_nome'] ?? null)] ); $this->stats['favorecidos']++; } } private function importContratos(): void { $this->command->info('Importando contratos...'); foreach ($this->readSpreadsheet(self::ODS_CONTRATOS) as $row) { $legacyId = trim((string) ($row['id_contrato'] ?? '')); if ($legacyId === '') { continue; } $contrato = Contrato::query()->create([ 'id' => (string) Str::uuid(), 'con_numero' => $this->nullableString($row['con_numero'] ?? null), 'con_ano' => $this->nullableString($row['con_ano'] ?? null), 'con_titulo' => $this->nullableString($row['con_titulo'] ?? null) ?? 'Sem titulo', 'con_modalidade' => $this->nullableInt($row['con_modalidade'] ?? null), 'con_favorecido' => $this->sanitizeDigits($row['con_favorecido'] ?? '', 14), 'con_dataini' => $this->normalizeDateString($row['con_dataini'] ?? null), 'con_datafim' => $this->normalizeDateString($row['con_datafim'] ?? null), 'con_valor' => $this->nullableString($row['con_valor'] ?? null), 'con_processo' => $this->nullableString($row['con_processo'] ?? null), 'con_secretaria' => $this->resolveSecretariaUuid($row['con_secretaria'] ?? null), 'con_secretaria_gestora' => $this->resolveSecretariaUuid($row['con_secretaria_gestora'] ?? null), 'con_datapub' => $this->normalizeDateString($row['con_datapub'] ?? null), 'con_fiscais' => $this->nullableString($row['con_fiscais'] ?? null), 'con_status' => $this->nullableInt($row['con_status'] ?? null), 'con_usuario' => $this->nullableUuid($row['con_usuario'] ?? null), 'con_covid' => $this->nullableString($row['con_covid'] ?? null), ]); $this->legacyContratoMap[$legacyId] = $contrato->id; $this->stats['contratos']++; } } private function importArquivos(): void { $this->command->info('Importando arquivos...'); $sequenciaPorContrato = []; foreach ($this->readSpreadsheet(self::ODS_ARQUIVOS) as $row) { $legacyContratoId = trim((string) ($row['arq_id_contrato'] ?? '')); $contratoId = $this->legacyContratoMap[$legacyContratoId] ?? null; if ($contratoId === null) { $this->stats['arquivos_sem_contrato']++; continue; } $sequencia = $this->nullableInt($row['arq_sequencia'] ?? null); if ($sequencia === null) { $sequenciaPorContrato[$contratoId] = ($sequenciaPorContrato[$contratoId] ?? 0) + 1; $sequencia = $sequenciaPorContrato[$contratoId]; } else { $sequenciaPorContrato[$contratoId] = max($sequenciaPorContrato[$contratoId] ?? 0, $sequencia); } $arquivo = $this->normalizeArquivoPath($row['arq_arquivo'] ?? null); if ($arquivo === null) { continue; } ContratoArquivo::query()->create([ 'arq_id_contrato' => $contratoId, 'arq_sequencia' => $sequencia, 'arq_arquivo' => $arquivo, 'arq_numero' => $this->nullableString($row['arq_numero'] ?? null), 'arq_titulo' => $this->nullableString($row['arq_titulo'] ?? null), 'arq_dataini' => $this->normalizeDateString($row['arq_dataini'] ?? null), 'arq_datafim' => $this->normalizeDateString($row['arq_datafim'] ?? null), ]); $this->stats['arquivos']++; } } private function showStats(): void { $this->command->info(''); $this->command->info('Resumo da importacao:'); $this->command->info('- Favorecidos: ' . $this->stats['favorecidos']); $this->command->info('- Contratos: ' . $this->stats['contratos']); $this->command->info('- Arquivos: ' . $this->stats['arquivos']); $this->command->info('- Arquivos sem contrato correspondente: ' . $this->stats['arquivos_sem_contrato']); $this->command->info('- Referencias de secretaria resolvidas: ' . $this->stats['secretarias_mapeadas']); $this->command->info('- Referencias de secretaria sem mapeamento: ' . $this->stats['secretarias_sem_mapeamento']); $this->command->info('==========================================='); } /* * @return \Generator<string, array<string, string\|null>> / private function readCsv(string $relativePath): \Generator { $path = base_path($relativePath); $handle = fopen($path, 'r'); if ($handle === false) { throw new \RuntimeException("Nao foi possivel abrir o arquivo: {$relativePath}"); } try { while (($row = fgetcsv($handle, separator: ';')) !== false) { if ($this->isEmptyRow($row) \|\| count($row) < 3) { continue; } yield [ 'id' => trim((string) ($row[0] ?? '')), 'sigla_antiga' => trim($this->normalizeSpreadsheetText((string) ($row[1] ?? ''))), 'sigla_atual' => trim($this->normalizeSpreadsheetText((string) ($row[2] ?? ''))), 'nome' => trim($this->normalizeSpreadsheetText((string) ($row[3] ?? ''))), ]; } } finally { fclose($handle); } } /* * @return \Generator<string, array<string, string\|null>> */ private function readSpreadsheet(string $relativePath): \Generator { $path = base_path($relativePath); $sheet = IOFactory::load($path)->getActiveSheet(); $rows = $sheet->toArray(null, true, true, true); if ($rows === []) { return; } $headerRow = array_shift($rows); $header = []; foreach ($headerRow as $value) { $column = trim($this->normalizeSpreadsheetText((string) $value), " \t\n\r\0\x0B\""); if ($column !== '') { $header[] = $column; } } foreach ($rows as $row) { $values = array_values($row); if ($this->isEmptyRow($values)) { continue; } $normalizedRow = []; foreach ($header as $index => $column) { $normalizedRow[$column] = isset($values[$index]) ? trim($this->normalizeSpreadsheetText((string) $values[$index]), " \t\n\r\0\x0B\"") : null; } yield $normalizedRow; } } private function isEmptyRow(array $row): bool { foreach ($row as $value) { if (trim((string) $value, " \t\n\r\0\x0B\"") !== '') { return false; } } return true; } private function sanitizeDigits(?string $value, int $maxLength): ?string { $digits = preg_replace('/\D+/', '', (string) $value); if ($digits === '') { return null; } return Str::limit($digits, $maxLength, ''); } private function nullableString(?string $value): ?string { $value = trim((string) $value); if ($value === '' \|\| strtoupper($value) === 'NULL') { return null; } return $value; } private function nullableInt(mixed $value): ?int { $value = trim((string) $value); if ($value === '' \|\| strtoupper($value) === 'NULL' \|\| !is_numeric($value)) { return null; } return (int) $value; } private function nullableUuid(?string $value): ?string { $value = trim((string) $value); if ($value === '' \|\| strtoupper($value) === 'NULL') { return null; } return Str::isUuid($value) ? $value : null; } private function normalizeDateString(?string $value): ?string { $value = trim((string) $value); if ($value === '' \|\| strtoupper($value) === 'NULL' \|\| $value === '00/00/0000' \|\| $value === '0') { return null; } if (preg_match('/^\d{2}\/\d{2}\/\d{4}$/', $value)) { return $value; } $parsed = \DateTime::createFromFormat('j-M-y', $value) ?: \DateTime::createFromFormat('d-M-y', $value); if ($parsed instanceof \DateTime) { return $parsed->format('d/m/Y'); } $timestamp = strtotime($value); if ($timestamp !== false) { return date('d/m/Y', $timestamp); } return $value; } private function normalizeArquivoPath(?string $value): ?string { $file = trim((string) $value); if ($file === '' \|\| strtoupper($file) === 'NULL') { return null; } $file = ltrim(str_replace('\\', '/', $file), '/'); if (str_starts_with($file, 'contratos/')) { return $file; } return 'contratos/' . $file; } private function normalizeSpreadsheetText(string $value): string { if ($value === '') { return ''; } if (mb_check_encoding($value, 'UTF-8')) { return $value; } $converted = @mb_convert_encoding($value, 'UTF-8', 'Windows-1252,ISO-8859-1,UTF-8'); return $converted !== false ? $converted : utf8_encode($value); } }

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