File manager

File manager - Edit - /var/www/html/portal/database/seeders/CronogramasCibersegurancaSeeder.php

Back
<?php namespace Database\Seeders; use App\Enums\Accounts\Cronogramas\CronogramaActivityStatus; use App\Enums\Accounts\Cronogramas\CronogramaProjectStatus; use App\Enums\Accounts\Cronogramas\CronogramaResponsibleType; use App\Models\Accounts\Cronogramas\Activity; use App\Models\Accounts\Cronogramas\ActivityResponsible; use App\Models\Accounts\Cronogramas\Dependency; use App\Models\Accounts\Cronogramas\Project; use App\Models\Empresa; use App\Models\Structure; use App\Models\UnidadeOrganizacional; use App\Models\User; use Carbon\Carbon; use Illuminate\Database\Seeder; use Illuminate\Support\Collection; use Illuminate\Support\Facades\DB; use PhpOffice\PhpSpreadsheet\Cell\Cell; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Shared\Date as XlsDate; /** * Importa o cronograma macro de Cibersegurança a partir da planilha XLSX. * * Idempotente: usa updateOrCreate em projetos/atividades (chave: projeto+código) * e firstOrCreate em dependências (chave: activity_id+predecessor_id). * * Execute: php artisan db:seed --class=CronogramasCibersegurancaSeeder / class CronogramasCibersegurancaSeeder extends Seeder { private const XLSX_PATH = 'temp/cronogramas/cronograma-exemplo.xlsx'; private const PROJECT_NAME = 'Cibersegurança'; /* * Mapa de tokens da planilha (coluna Responsável) para responsáveis reais. * Chave em caixa alta sem espaços extras. * * @var array<string, array{type: string, id: string}> / private const RESPONSIBLE_MAP = [ 'HTS' => [ 'type' => 'unidade', 'id' => 'c3d8ae3a-62f2-4f38-8c70-a2d176209f74', ], 'PREFEITURA' => [ 'type' => 'unidade', 'id' => '54fea1f6-d260-490a-8004-dd3fa53a4444', ], ]; /* @var array<string, string> código-ITEM => UUID da atividade criada / private array $codeToId = []; /* @var array<string, string[]> activityId => [predecessorCodes...] / private array $pendingDeps = []; private array $stats = [ 'parents' => 0, 'children' => 0, 'dependencies' => 0, 'skipped_deps' => 0, 'skipped_responsibles' => 0, 'dates_failed' => 0, 'restored' => 0, 'deduped' => 0, 'rollup_dates' => 0, ]; private readonly array $statusMap; public function __construct() { $this->statusMap = [ 'concluído' => CronogramaActivityStatus::Completed->value, 'concluido' => CronogramaActivityStatus::Completed->value, 'em andamento' => CronogramaActivityStatus::InProgress->value, 'em andamento' => CronogramaActivityStatus::InProgress->value, 'não iniciado' => CronogramaActivityStatus::NotStarted->value, 'nao iniciado' => CronogramaActivityStatus::NotStarted->value, 'suspenso' => CronogramaActivityStatus::Suspended->value, 'cancelado' => CronogramaActivityStatus::Cancelled->value, 'em validação' => CronogramaActivityStatus::Validation->value, 'em validacao' => CronogramaActivityStatus::Validation->value, 'homologação' => CronogramaActivityStatus::Validation->value, 'homologacao' => CronogramaActivityStatus::Validation->value, ]; } // ───────────────────────────────────────────────────────────────────────── public function run(): void { $this->banner(); // ── 1. Usuário responsável ────────────────────────────────────────── $user = $this->resolveUser(); if (! $user) { $this->command->error('Nenhum usuário encontrado. Crie ao menos um usuário antes de rodar este seeder.'); return; } $this->line(" Responsável : <fg=cyan>{$user->name}</> ({$user->id})"); // ── 2. Carregar planilha ──────────────────────────────────────────── $this->line(''); $this->line(' <fg=yellow>▶</> Lendo planilha XLSX...'); $rows = $this->loadXlsx(); $total = $rows->count(); $this->line(" <fg=green>✓</> {$total} linhas de dados identificadas."); // ── 3. Projeto ────────────────────────────────────────────────────── $this->line(''); $this->line(' <fg=yellow>▶</> Projeto...'); $project = $this->upsertProject($user); $verb = $project->wasRecentlyCreated ? 'criado' : 'encontrado'; $this->line(" <fg=green>✓</> Projeto \"{$project->name}\" {$verb} [{$project->id}]"); // ── 4. Separar pais e filhos ──────────────────────────────────────── $parents = $rows->filter(fn ($r) => str_ends_with((string) $r['code'], '.0'))->values(); $children = $rows->reject(fn ($r) => str_ends_with((string) $r['code'], '.0'))->values(); // ── 5. Importar ───────────────────────────────────────────────────── $this->line(''); $this->line(" <fg=yellow>▶</> Etapa 1/3 — Atividades pai ({$parents->count()})..."); $barParents = $this->command->getOutput()->createProgressBar($parents->count()); $barParents->setFormat(' %current%/%max% [%bar%] %percent:3s%% — %message%'); $barParents->setMessage(''); $barParents->start(); DB::transaction(function () use ($parents, $children, $project, $barParents): void { foreach ($parents as $sort => $row) { $barParents->setMessage($row['code'] . ' ' . mb_substr($row['title'], 0, 40)); $this->upsertActivity($row, $project->id, null, ($sort + 1) 10); $this->stats['parents']++; $barParents->advance(); } $barParents->finish(); $this->line(''); // ── Filhos ────────────────────────────────────────────────────── $this->line(''); $this->line(" <fg=yellow>▶</> Etapa 2/3 — Atividades filhas ({$children->count()})..."); $barChildren = $this->command->getOutput()->createProgressBar($children->count()); $barChildren->setFormat(' %current%/%max% [%bar%] %percent:3s%% — %message%'); $barChildren->setMessage(''); $barChildren->start(); foreach ($children as $sort => $row) { $barChildren->setMessage($row['code'] . ' ' . mb_substr($row['title'], 0, 40)); $parentCode = $this->resolveParentCode($row['code']); $parentId = $this->codeToId[$parentCode] ?? null; $this->upsertActivity($row, $project->id, $parentId, ($sort + 1) * 10); $this->stats['children']++; $barChildren->advance(); } $barChildren->finish(); $this->line(''); // ── Dependências ──────────────────────────────────────────────── $depCount = array_sum(array_map('count', $this->pendingDeps)); $this->line(''); $this->line(" <fg=yellow>▶</> Etapa 3/3 — Dependências ({$depCount} vínculos)..."); $barDeps = $this->command->getOutput()->createProgressBar(count($this->pendingDeps)); $barDeps->setFormat(' %current%/%max% [%bar%] %percent:3s%%'); $barDeps->start(); foreach ($this->pendingDeps as $activityId => $predCodes) { foreach ($predCodes as $predCode) { $predId = $this->codeToId[$predCode] ?? null; if (! $predId) { $this->stats['skipped_deps']++; continue; } Dependency::firstOrCreate( ['activity_id' => $activityId, 'predecessor_id' => $predId] ); $this->stats['dependencies']++; } $barDeps->advance(); } $barDeps->finish(); $this->line(''); $this->dedupeImportedActivities($project->id); $this->rollupParentDates($project->id); }); // ── 6. Recalcular percentual do projeto ───────────────────────────── $this->recalcProject($project); // ── 7. Resumo ─────────────────────────────────────────────────────── $this->summary($project); } // ── Núcleo ─────────────────────────────────────────────────────────────── private function upsertActivity(array $row, string $projectId, ?string $parentId, int $sort): void { ['mapped' => $mappedResponsibles] = $this->parseResponsibles($row['responsible']); // A Observação reflete exclusivamente a coluna Observação da planilha. // Dados de responsável NÃO são misturados aqui (vão para a tabela de responsáveis). $notes = $row['notes'] ?: null; $activity = Activity::withTrashed()->updateOrCreate( ['project_id' => $projectId, 'code' => $row['code']], [ 'title' => $row['title'], 'description' => $row['description'] ?: null, 'notes' => $notes, 'parent_id' => $parentId, 'status' => $row['status'], 'completion_percent' => $row['percent'], 'planned_start_date' => $row['start_date'], 'planned_end_date' => $row['end_date'], 'sort_order' => $sort, ] ); if ($activity->trashed()) { $activity->restore(); $this->stats['restored']++; } $this->syncResponsibles($activity->id, $mappedResponsibles); $this->codeToId[$row['code']] = $activity->id; if (! empty($row['predecessor'])) { $this->pendingDeps[$activity->id] = $this->parsePredecessors($row['predecessor']); } } /** * Interpreta a coluna de responsável da planilha, separando tokens conhecidos * (mapeados para unidades/órgãos reais) dos desconhecidos (mantidos em nota). * * @return array{mapped: list<array{type: string, id: string}>, unknown: list<string>} / private function parseResponsibles(?string $raw): array { $mapped = []; $unknown = []; $seen = []; // Separadores: / , ; & + quebra de linha, " e ", e hífen entre espaços // (ex.: "HTS - PREFEITURA" → ["HTS", "PREFEITURA"]). Inclui en/em dash. $tokens = preg_split('/[\/,;&+\n]+\|\s+e\s+\|\s+[-\x{2013}\x{2014}]\s+/iu', (string) $raw) ?: []; foreach ($tokens as $token) { $token = trim($token); if ($token === '') { continue; } $key = mb_strtoupper($token); if (isset(self::RESPONSIBLE_MAP[$key])) { $pair = self::RESPONSIBLE_MAP[$key]; $dedupe = $pair['type'] . '\|' . $pair['id']; if (! isset($seen[$dedupe])) { $seen[$dedupe] = true; $mapped[] = $pair; } continue; } $unknown[] = $token; } return ['mapped' => $mapped, 'unknown' => $unknown]; } /* * Substitui os responsáveis da atividade na tabela pivô. * * @param list<array{type: string, id: string}> $responsibles / private function syncResponsibles(string $activityId, array $responsibles): void { ActivityResponsible::where('activity_id', $activityId)->delete(); foreach ($responsibles as $pair) { // Evita vínculos órfãos: só cria se a entidade responsável existir. if (! $this->responsibleEntityExists($pair['type'], $pair['id'])) { $this->stats['skipped_responsibles']++; continue; } ActivityResponsible::create([ 'activity_id' => $activityId, 'responsible_type' => $pair['type'], 'responsible_id' => $pair['id'], ]); } } /* * Verifica se a entidade responsável realmente existe no banco. / private function responsibleEntityExists(string $type, string $id): bool { $modelClass = match (CronogramaResponsibleType::tryFrom($type)) { CronogramaResponsibleType::User => User::class, CronogramaResponsibleType::Empresa => Empresa::class, CronogramaResponsibleType::Structure => Structure::class, CronogramaResponsibleType::Unidade => UnidadeOrganizacional::class, default => null, }; if ($modelClass === null) { return false; } return $modelClass::query()->whereKey($id)->exists(); } private function upsertProject(User $user): Project { return Project::updateOrCreate( ['name' => self::PROJECT_NAME], [ 'user_id' => $user->id, 'description' => 'Cronograma macro de implementação de Cibersegurança - importado via planilha XLSX.', 'status' => CronogramaProjectStatus::Active->value, 'start_date' => '2026-04-17', 'completion_percent' => 0, ] ); } private function recalcProject(Project $project): void { $avg = Activity::where('project_id', $project->id) ->whereNull('parent_id') ->whereNull('deleted_at') ->avg('completion_percent') ?? 0; $project->update(['completion_percent' => round((float) $avg, 2)]); } /* * Remove registros duplicados (mesmo código) mantendo o canônico importado. / private function dedupeImportedActivities(string $projectId): void { foreach ($this->codeToId as $code => $canonicalId) { $removed = Activity::withTrashed() ->where('project_id', $projectId) ->where('code', $code) ->where('id', '!=', $canonicalId) ->count(); if ($removed > 0) { Activity::withTrashed() ->where('project_id', $projectId) ->where('code', $code) ->where('id', '!=', $canonicalId) ->forceDelete(); $this->stats['deduped'] += $removed; } } } /* * Preenche início/término de atividades pai com base nos filhos, quando vazios na planilha. / private function rollupParentDates(string $projectId): void { $parents = Activity::query() ->where('project_id', $projectId) ->whereNull('deleted_at') ->whereNull('parent_id') ->get(); foreach ($parents as $parent) { $children = Activity::query() ->where('parent_id', $parent->id) ->whereNull('deleted_at') ->get(); if ($children->isEmpty()) { continue; } $updates = []; if ($parent->planned_start_date === null) { $minStart = $children ->pluck('planned_start_date') ->filter() ->min(); if ($minStart !== null) { $updates['planned_start_date'] = $minStart; } } if ($parent->planned_end_date === null) { $maxEnd = $children ->pluck('planned_end_date') ->filter() ->max(); if ($maxEnd !== null) { $updates['planned_end_date'] = $maxEnd; } } if ($updates !== []) { $parent->update($updates); $this->stats['rollup_dates']++; } } } // ── Leitura da planilha ─────────────────────────────────────────────────── private function loadXlsx(): Collection { $spreadsheet = IOFactory::load(base_path(self::XLSX_PATH)); $sheet = $spreadsheet->getActiveSheet(); $rows = collect(); $maxRow = $sheet->getHighestDataRow(); for ($i = 4; $i <= $maxRow; $i++) { $code = $this->cellText($sheet->getCell('B' . $i)); $title = $this->cellText($sheet->getCell('C' . $i)); // Pular cabeçalhos repetidos, linhas separadoras e vazias if ( $code === '' \|\| mb_strtoupper($code) === 'ITEM' \|\| $title === '' \|\| mb_strtoupper($title) === 'ATIVIDADES' ) { continue; } $predRaw = $this->cellText($sheet->getCell('D' . $i)); $percRaw = $sheet->getCell('E' . $i)->getCalculatedValue(); $statRaw = $this->cellText($sheet->getCell('F' . $i)); $respRaw = $this->cellText($sheet->getCell('G' . $i)); $descRaw = $this->cellText($sheet->getCell('J' . $i)); $obsRaw = $this->cellText($sheet->getCell('K' . $i)); if (mb_strtoupper($obsRaw) === 'OBSERVAÇÃO' \|\| mb_strtoupper($obsRaw) === 'OBSERVACAO') { $obsRaw = ''; } $startDate = $this->extractDate($sheet->getCell('H' . $i)); $endDate = $this->extractDate($sheet->getCell('I' . $i)); $rows->push([ 'code' => $code, 'title' => $title, 'predecessor' => $predRaw, 'percent' => is_numeric($percRaw) ? (float) $percRaw : 0.0, 'status' => $this->mapStatus($statRaw), 'responsible' => $respRaw, 'start_date' => $startDate, 'end_date' => $endDate, 'description' => $descRaw, 'notes' => $obsRaw, ]); } return $rows; } private function cellText(Cell $cell): string { $value = $cell->getCalculatedValue(); if ($value === null \|\| $value === false) { $value = $cell->getFormattedValue(); } $text = trim(preg_replace('/\s+/u', ' ', (string) $value) ?? ''); return $text; } // ── Datas ───────────────────────────────────────────────────────────────── /* * Extrai data da célula — suporta serial Excel e strings em vários formatos. / private function extractDate(Cell $cell): ?string { $value = $cell->getValue(); if ($value === null \|\| $value === '' \|\| $value === false) return null; // Serial numérico do Excel com formato de data if (is_numeric($value) && XlsDate::isDateTime($cell)) { try { $dt = XlsDate::excelToDateTimeObject((float) $value); return Carbon::instance($dt)->format('Y-m-d'); } catch (\Throwable) { $this->stats['dates_failed']++; return null; } } // Texto return $this->parseTextDate((string) $value); } private function parseTextDate(string $raw): ?string { $str = trim($raw); if ($str === '' \|\| in_array(mb_strtolower($str), ['a definir', '-', 'n/a', 'nd'])) { return null; } // Normaliza barras duplas: "29//05/26" → "29/05/26" $str = preg_replace('/\/+/', '/', $str); // "28/Apr", "30/May", "20-Apr", "31-May" — sem ano, assume 2026 if (preg_match('/^(\d{1,2})[\/\-]([A-Za-z]{3})$/i', $str, $m)) { try { return Carbon::createFromFormat( 'd/M/Y', sprintf('%02d/%s/2026', (int) $m[1], ucfirst(strtolower($m[2]))) )->format('Y-m-d'); } catch (\Throwable) {} } // "20-Apr-26", "30-May-2026" if (preg_match('/^(\d{1,2})[\/\-]([A-Za-z]{3})[\/\-](\d{2,4})$/i', $str, $m)) { $year = strlen($m[3]) === 2 ? '20' . $m[3] : $m[3]; try { return Carbon::createFromFormat( 'd/M/Y', sprintf('%02d/%s/%s', (int) $m[1], ucfirst(strtolower($m[2])), $year) )->format('Y-m-d'); } catch (\Throwable) {} } $slashParsed = $this->parseSlashDate($str); if ($slashParsed !== null) { return $slashParsed; } foreach (['Y-m-d', 'Y/m/d'] as $fmt) { try { $p = Carbon::createFromFormat($fmt, $str); if ($p && $p->year >= 2020 && $p->year <= 2035) { return $p->format('Y-m-d'); } } catch (\Throwable) {} } $this->stats['dates_failed']++; return null; } /* * Interpreta datas numéricas com barra, distinguindo d/m e m/d. / private function parseSlashDate(string $str): ?string { if (! preg_match('/^(\d{1,2})\/(\d{1,2})\/(\d{2,4})$/', $str, $m)) { return null; } $partA = (int) $m[1]; $partB = (int) $m[2]; $year = strlen($m[3]) === 2 ? 2000 + (int) $m[3] : (int) $m[3]; if ($year < 2020 \|\| $year > 2035) { return null; } $candidates = []; if ($partA > 12 && $partB <= 12) { $candidates[] = [$year, $partB, $partA]; } elseif ($partB > 12 && $partA <= 12) { $candidates[] = [$year, $partA, $partB]; } elseif ($partA <= 12 && $partB <= 12) { $candidates[] = [$year, $partA, $partB]; $candidates[] = [$year, $partB, $partA]; } else { return null; } foreach ($candidates as [$y, $month, $day]) { if ($month < 1 \|\| $month > 12 \|\| $day < 1 \|\| $day > 31) { continue; } try { return Carbon::create($y, $month, $day)->format('Y-m-d'); } catch (\Throwable) {} } return null; } // ── Helpers ─────────────────────────────────────────────────────────────── private function resolveUser(): ?User { return User::orderBy('created_at')->first(); } private function mapStatus(string $raw): string { $key = mb_strtolower(trim($raw)); return $this->statusMap[$key] ?? CronogramaActivityStatus::NotStarted->value; } /* * "1.5" → "1.0", "3.12" → "3.0" / private function resolveParentCode(string $code): string { return explode('.', $code)[0] . '.0'; } /* * "1.2, 1.3" ou "6.3, 6.4, 6.5, 6.6" → ['1.2', '1.3'] */ private function parsePredecessors(string $raw): array { return array_values(array_filter( array_map('trim', explode(',', str_replace(';', ',', $raw))), fn ($c) => $c !== '' )); } // ── Output ──────────────────────────────────────────────────────────────── private function banner(): void { $this->line(''); $this->line(' <fg=blue>╔══════════════════════════════════════════════════════╗</>'); $this->line(' <fg=blue>║</> <fg=white;options=bold>Seeder — Cronograma Cibersegurança (XLSX)</> <fg=blue>║</>'); $this->line(' <fg=blue>╚══════════════════════════════════════════════════════╝</>'); $this->line(''); } private function summary(Project $project): void { $totalAct = Activity::where('project_id', $project->id)->count(); $totalDep = Dependency::whereIn( 'activity_id', Activity::where('project_id', $project->id)->pluck('id') )->count(); $this->line(''); $this->line(' <fg=blue>╔══════════════════════════════════════════════════════╗</>'); $this->line(' <fg=blue>║</> <fg=green;options=bold>✅ Importação concluída!</> <fg=blue>║</>'); $this->line(' <fg=blue>╠══════════════════════════════════════════════════════╣</>'); $this->line(" <fg=blue>║</> Atividades pai : <fg=cyan>{$this->stats['parents']}</>"); $this->line(" <fg=blue>║</> Atividades filhas : <fg=cyan>{$this->stats['children']}</>"); $this->line(" <fg=blue>║</> Total atividades : <fg=cyan>{$totalAct}</>"); $this->line(" <fg=blue>║</> Dependências : <fg=cyan>{$totalDep}</>"); if ($this->stats['skipped_deps'] > 0) { $this->line(" <fg=blue>║</> Deps não resolvidas: <fg=yellow>{$this->stats['skipped_deps']}</>"); } if ($this->stats['skipped_responsibles'] > 0) { $this->line(" <fg=blue>║</> Responsáveis ignorados (inexistentes): <fg=yellow>{$this->stats['skipped_responsibles']}</>"); } if ($this->stats['dates_failed'] > 0) { $this->line(" <fg=blue>║</> Datas não parseadas : <fg=yellow>{$this->stats['dates_failed']}</>"); } if ($this->stats['restored'] > 0) { $this->line(" <fg=blue>║</> Atividades restauradas: <fg=cyan>{$this->stats['restored']}</>"); } if ($this->stats['deduped'] > 0) { $this->line(" <fg=blue>║</> Duplicatas removidas : <fg=cyan>{$this->stats['deduped']}</>"); } if ($this->stats['rollup_dates'] > 0) { $this->line(" <fg=blue>║</> Pais com datas rollup: <fg=cyan>{$this->stats['rollup_dates']}</>"); } $withDates = Activity::where('project_id', $project->id) ->whereNull('deleted_at') ->where(function ($q): void { $q->whereNotNull('planned_start_date') ->orWhereNotNull('planned_end_date'); }) ->count(); $withDesc = Activity::where('project_id', $project->id) ->whereNull('deleted_at') ->whereNotNull('description') ->where('description', '!=', '') ->count(); $withNotes = Activity::where('project_id', $project->id) ->whereNull('deleted_at') ->whereNotNull('notes') ->where('notes', '!=', '') ->count(); $this->line(" <fg=blue>║</> Com datas (ativas) : <fg=cyan>{$withDates}</>"); $this->line(" <fg=blue>║</> Com descrição : <fg=cyan>{$withDesc}</>"); $this->line(" <fg=blue>║</> Com observações : <fg=cyan>{$withNotes}</>"); $this->line(" <fg=blue>║</> Conclusão projeto : <fg=cyan>{$project->fresh()->completion_percent}%</>"); $this->line(' <fg=blue>╚══════════════════════════════════════════════════════╝</>'); $this->line(''); } private function line(string $msg): void { $this->command->line($msg); } }

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