File manager

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

Back
<?php namespace Database\Seeders; use Illuminate\Database\Seeder; use Illuminate\Support\Facades\DB; use Illuminate\Support\Str; class CartaServicosImportSeeder extends Seeder { private const CSV_SERVICOS_PATH = 'temp/carta-de-servicos/CARTAS.csv'; private const CSV_TIPOS_PATH = 'temp/carta-de-servicos/TIPOS.csv'; private const BATCH_SIZE = 300; private const SECRETARIA_MIN_SCORE = 35; public function run(): void { $this->command->info('==========================================='); $this->command->info('Importando Carta de Servicos (TIPOS + SERVICOS)...'); $this->command->info('==========================================='); $this->validateFiles(); DB::beginTransaction(); try { $this->command->info('Limpando tabelas de destino...'); DB::table('carta_servicos')->delete(); DB::table('carta_servicos_tipos')->delete(); $importedTipos = $this->importTipos(); $validCategoriaIds = DB::table('carta_servicos_tipos') ->pluck('id_tipo') ->map(static fn ($id): int => (int) $id) ->all(); $validCategoriaIds = array_fill_keys($validCategoriaIds, true); $rows = iterator_to_array($this->readCsv(self::CSV_SERVICOS_PATH), false); $legacySecretariaMap = $this->buildLegacySecretariaMap($rows); $batch = []; $imported = 0; $now = now(); foreach ($rows as $row) { $titulo = $this->nullableString($row['cds_titulo'] ?? null); if ($titulo === null) { continue; } $batch[] = [ 'cds_secretaria' => $this->resolveSecretariaUuid($row, $legacySecretariaMap), 'categoria_principal_id' => $this->extractCategoriaPrincipalId($row['cds_tipo'] ?? null, $validCategoriaIds), 'cds_setor' => $this->nullableString($row['cds_setor'] ?? null), 'cds_entrada' => $this->nullableString($row['cds_entrada'] ?? null), 'cds_titulo' => Str::limit($titulo, 255, ''), 'cds_tipo' => $this->nullableString($row['cds_tipo'] ?? null), 'cds_descricao' => $this->nullableString($row['cds_descricao'] ?? null), 'cds_telefone' => Str::limit((string) ($this->nullableString($row['cds_telefone'] ?? null) ?? ''), 120, '') ?: null, 'cds_email' => $this->normalizeEmailForStorage($row['cds_email'] ?? null), 'cds_quem' => $this->nullableString($row['cds_quem'] ?? null), 'cds_quando' => $this->nullableString($row['cds_quando'] ?? null), 'cds_atendimento' => $this->nullableString($row['cds_atendimento'] ?? null), 'cds_requisitos' => $this->nullableString($row['cds_requisitos'] ?? null), 'cds_prazo' => $this->nullableString($row['cds_prazo'] ?? null), 'cds_taxas' => $this->nullableString($row['cds_taxas'] ?? null), 'cds_legislacao' => $this->nullableString($row['cds_legislacao'] ?? null), 'cds_contato' => $this->nullableString($row['cds_contato'] ?? null), 'cds_etapas' => $this->nullableString($row['cds_etapas'] ?? null), 'cds_obs' => $this->nullableString($row['cds_obs'] ?? null), 'cds_solicitacao' => $this->normalizeUrlForStorage($row['cds_solicitacao'] ?? null, 500), 'cds_status' => $this->normalizeStatus($row['cds_status'] ?? null), 'cds_mapa' => $this->normalizeUrlForStorage($row['cds_mapa'] ?? null, 1000), 'created_at' => $now, 'updated_at' => $now, ]; if (count($batch) >= self::BATCH_SIZE) { DB::table('carta_servicos')->insert($batch); $imported += count($batch); $batch = []; } } if ($batch !== []) { DB::table('carta_servicos')->insert($batch); $imported += count($batch); } DB::commit(); $mappedSecretarias = count(array_filter($legacySecretariaMap)); $this->command->info("Tipos importados: {$importedTipos}"); $this->command->info("Mapeamento de secretarias legado aplicado: {$mappedSecretarias}"); $this->command->info("Importacao finalizada. Registros inseridos: {$imported}"); } catch (\Throwable $e) { DB::rollBack(); $this->command->error('Falha na importacao da Carta de Servicos: ' . $e->getMessage()); throw $e; } } private function validateFiles(): void { foreach ([self::CSV_TIPOS_PATH, self::CSV_SERVICOS_PATH] as $file) { if (!file_exists(base_path($file))) { throw new \RuntimeException('Arquivo CSV nao encontrado em: ' . $file); } } } private function importTipos(): int { $rows = []; $now = now(); foreach ($this->readCsv(self::CSV_TIPOS_PATH) as $row) { $id = (int) preg_replace('/\D+/', '', (string) ($row['id_tipo'] ?? '0')); if ($id <= 0) { continue; } $descricao = $this->nullableString($row['tip_descricao'] ?? null); if ($descricao === null) { continue; } $status = strtoupper(trim((string) ($row['tip_status'] ?? 'A'))); if (!in_array($status, ['A', 'I'], true)) { $status = 'A'; } $rows[] = [ 'id_tipo' => $id, 'tip_descricao' => Str::limit($descricao, 255, ''), 'tip_class' => Str::limit((string) ($this->nullableString($row['tip_class'] ?? null) ?? 'fa-circle'), 100, ''), 'tip_status' => $status, 'created_at' => $now, 'updated_at' => $now, ]; } if ($rows === []) { return 0; } DB::table('carta_servicos_tipos')->insert($rows); return count($rows); } 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 { $header = fgetcsv($handle, separator: ';'); if ($header === false) { return; } $header = array_map(function ($value) { $normalized = $this->normalizeCsvText((string) $value); $normalized = preg_replace('/^\xEF\xBB\xBF/', '', $normalized); return trim((string) $normalized, " \t\n\r\0\x0B\""); }, $header); while (($row = fgetcsv($handle, separator: ';')) !== false) { if ($this->isEmptyRow($row)) { continue; } $normalizedRow = []; foreach ($header as $index => $column) { $normalizedRow[$column] = isset($row[$index]) ? trim($this->normalizeCsvText((string) $row[$index]), " \t\n\r\0\x0B\"") : null; } yield $normalizedRow; } } finally { fclose($handle); } } private function extractCategoriaPrincipalId(?string $rawTipo, array $validCategoriaIds): ?int { if ($rawTipo === null \|\| trim($rawTipo) === '') { return null; } preg_match_all('/\d+/', $rawTipo, $matches); foreach ($matches[0] as $possibleId) { $id = (int) $possibleId; if (isset($validCategoriaIds[$id])) { return $id; } } return null; } private function normalizeStatus(?string $value): string { $status = trim((string) $value); if (!in_array($status, ['1', '2', '3'], true)) { return '3'; } return $status; } private function nullableUuid(?string $value): ?string { $value = trim((string) $value); return Str::isUuid($value) ? $value : null; } private function nullableString(?string $value): ?string { $value = trim((string) $value); return $value !== '' ? $value : null; } 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 normalizeCsvText(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); } private function normalizeEmailForStorage(?string $value): ?string { $value = $this->nullableString($value); if ($value === null) { return null; } $emails = $this->extractEmails($value); if ($emails === []) { return null; } return Str::limit($emails[0], 255, ''); } private function normalizeUrlForStorage(?string $value, int $maxLength): ?string { $url = $this->nullableString($value); if ($url === null) { return null; } $url = trim($url); if (!preg_match('/^https?:\/\//i', $url)) { return null; } if (!filter_var($url, FILTER_VALIDATE_URL)) { return null; } return Str::limit($url, $maxLength, ''); } private function resolveSecretariaUuid(array $row, array $legacySecretariaMap): ?string { $currentValue = $this->nullableUuid($row['cds_secretaria'] ?? null); if ($currentValue !== null) { return $currentValue; } $legacyId = preg_replace('/\D+/', '', (string) ($row['cds_secretaria'] ?? '')); if ($legacyId === '') { return null; } return $legacySecretariaMap[$legacyId] ?? null; } private function buildLegacySecretariaMap(array $rows): array { $structures = DB::table('structures') ->select(['id', 'name', 'abbreviation', 'slug', 'email1', 'email2', 'email3']) ->get() ->map(function ($item) { $emails = array_values(array_filter([ $this->normalizeEmail($item->email1), $this->normalizeEmail($item->email2), $this->normalizeEmail($item->email3), ])); return [ 'id' => (string) $item->id, 'tokens' => $this->extractSearchTokens( implode(' ', [ (string) $item->name, (string) $item->abbreviation, (string) $item->slug, implode(' ', $emails), ]) ), 'emails' => $emails, ]; }) ->all(); $scoreByLegacy = []; foreach ($rows as $row) { $legacyId = preg_replace('/\D+/', '', (string) ($row['cds_secretaria'] ?? '')); if ($legacyId === '') { continue; } $sourceText = implode(' ', [ (string) ($row['cds_titulo'] ?? ''), (string) ($row['cds_entrada'] ?? ''), (string) ($row['cds_email'] ?? ''), ]); $sourceTokens = array_fill_keys($this->extractSearchTokens($sourceText), true); $sourceEmails = $this->extractEmails((string) ($row['cds_email'] ?? '')); foreach ($structures as $structure) { $score = 0; foreach ($sourceEmails as $email) { if (in_array($email, $structure['emails'], true)) { $score += 120; } elseif ($email !== '' && str_contains(implode(' ', $structure['emails']), $email)) { $score += 80; } } foreach ($structure['tokens'] as $token) { if (isset($sourceTokens[$token])) { $score += strlen($token) >= 8 ? 9 : 5; } } if ($score > 0) { $scoreByLegacy[$legacyId][$structure['id']] = ($scoreByLegacy[$legacyId][$structure['id']] ?? 0) + $score; } } } $resolved = []; foreach ($scoreByLegacy as $legacyId => $scoreByStructure) { arsort($scoreByStructure); $bestStructureId = array_key_first($scoreByStructure); $bestScore = $scoreByStructure[$bestStructureId] ?? 0; if ($bestScore >= self::SECRETARIA_MIN_SCORE) { $resolved[$legacyId] = $bestStructureId; } } return $resolved; } private function normalizeEmail(?string $value): string { return strtolower(trim((string) $value)); } private function extractEmails(string $value): array { preg_match_all('/[a-z0-9._%+\-]+@[a-z0-9.\-]+\.[a-z]{2,}/i', strtolower($value), $matches); return array_values(array_unique($matches[0] ?? [])); } private function extractSearchTokens(string $value): array { $value = Str::ascii(mb_strtolower($value)); $parts = preg_split('/[^a-z0-9]+/', $value) ?: []; $tokens = []; foreach ($parts as $part) { $part = trim($part); if (strlen($part) >= 4) { $tokens[] = $part; } } return array_values(array_unique($tokens)); } }

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