AdminGit2026/nis2-agile
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
nis2-agile/application/controllers/InternalAuditController.php
T
DevEnv nis2-agileandClaude Opus 4.8 2b6c5087ad [DB] Hardening integrità chiavi (mig.058): +4 UNIQUE +8 FK, retry-on-1062, bonifica 16 righe stale — v1.23.1 
Estende l'audit mig.039 ai moduli 040-057 (non coperti).
TIER1 additivo (0 dup/0 orfani verificati su DB live):
- UNIQUE internal_audits/management_reviews (org,code), kb_uploaded_documents.qdrant_doc_uuid,
  whistleblowing_reports.anonymous_token (drop idx_token ridondante)
- retry-on-1062 in InternalAuditController/ManagementReviewController (allineati a periodic_controls)
- 6 FK: consulting_firm_id (organizations/users/kb)->consulting_firms; isms_soa.linked_control_id;
  isms_documents.linked_policy_id; management_review_decisions.capa_id (tutte SET NULL)
TIER2 bonifica (backup pre-DELETE in .backups/): -9 firm_org_assignments orfane (org 126-129,
  chiude deferred-b mig.039) -7 active_sessions scadute, +2 FK CASCADE.
FK totali 196->204. Sonda diagnostica db_integrity_probe.php. audit_logs lasciato by-design.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
2026-06-18 07:57:25 +02:00

646 lines
36 KiB
PHP
Raw Permalink Blame History

<?php
/**
* NIS2 Agile - Audit interni (ISO 27001 §9.2) — MODULO A
* ----------------------------------------------------------------------------
* Ciclo audit interno: programma -> checklist di conduzione -> esiti -> finding NC.
* - PROGRAMMA: internal_audits (code AUD-NNN progressivo per org, scope, criteri,
* auditor capo come utente o ruolo organigramma, date pianificate/eseguite,
* stato, conclusione). La data pianificata alimenta il calendario (review_schedule).
* - CHECKLIST: internal_audit_items pre-popolata al create con le clausole 4-10
* ISO 27001 (lista statica) + i controlli Annex A applicabili dal SoA dell'org
* (isms_soa.applicable=1, query difensiva: la tabella potrebbe non esistere).
* - FINDING: una voce non_conforme può generare una NC (non_conformities,
* source='audit', source_entity_type='internal_audit_item'), che confluisce
* nel modulo NCR/CAPA esistente.
* - EVIDENZE: su evidence_files (entity_type='internal_audit') — gestite altrove.
* - REPORT: HTML stampabile (no PDF lib).
*
* Multi-tenancy: ogni query filtra organization_id. Anti-IDOR su audit/item/role
* (verificati appartenenti all'org). logAudit su create/update/delete.
* NOTE: Database::query/fetchAll/fetchOne/insert/update/delete; jsonSuccess/Error exit.
*/
require_once __DIR__ . '/BaseController.php';
class InternalAuditController extends BaseController
{
private const MANAGE_ROLES = ['org_admin', 'compliance_manager', 'auditor'];
/**
* Clausole 4-10 ISO/IEC 27001:2022 — checklist statica (ref_type='clause').
* Pre-popolata al create di ogni audit; l'auditor parte già con la checklist.
*/
private const ISO_CLAUSES = [
['4.1', 'Comprensione dell\'organizzazione e del suo contesto'],
['4.2', 'Comprensione delle esigenze e aspettative delle parti interessate'],
['4.3', 'Determinazione dello scopo del SGSI'],
['4.4', 'Sistema di gestione per la sicurezza delle informazioni'],
['5.1', 'Leadership e impegno della direzione'],
['5.2', 'Politica per la sicurezza delle informazioni'],
['5.3', 'Ruoli, responsabilità e autorità organizzative'],
['6.1.1', 'Azioni per affrontare rischi e opportunità — generalità'],
['6.1.2', 'Valutazione del rischio per la sicurezza delle informazioni'],
['6.1.3', 'Trattamento del rischio per la sicurezza delle informazioni (SoA)'],
['6.2', 'Obiettivi di sicurezza delle informazioni e pianificazione'],
['6.3', 'Pianificazione delle modifiche'],
['7.1', 'Risorse'],
['7.2', 'Competenza'],
['7.3', 'Consapevolezza'],
['7.4', 'Comunicazione'],
['7.5', 'Informazioni documentate'],
['8.1', 'Pianificazione e controllo operativi'],
['8.2', 'Valutazione del rischio per la sicurezza delle informazioni'],
['8.3', 'Trattamento del rischio per la sicurezza delle informazioni'],
['9.1', 'Monitoraggio, misurazione, analisi e valutazione'],
['9.2', 'Audit interno'],
['9.3', 'Riesame di direzione'],
['10.1', 'Miglioramento continuo'],
['10.2', 'Non conformità e azioni correttive'],
];
// ─────────────────────────────────────────────────────────────────────────
// PROGRAMMA AUDIT
// ─────────────────────────────────────────────────────────────────────────
/** GET /api/internal-audits/list */
public function list(): void
{
$this->requireOrgAccess();
$orgId = $this->getCurrentOrgId();
$rows = Database::fetchAll(
'SELECT a.id, a.code, a.title, a.status, a.planned_date, a.executed_date,
a.lead_auditor_user_id, u.full_name AS lead_auditor_name,
a.lead_auditor_role_id, r.role_name AS lead_auditor_role_name, a.updated_at,
(SELECT COUNT(*) FROM internal_audit_items i WHERE i.audit_id = a.id) AS n_items,
(SELECT COUNT(*) FROM internal_audit_items i WHERE i.audit_id = a.id AND i.result = \'non_conforme\') AS n_nc
FROM internal_audits a
LEFT JOIN users u ON u.id = a.lead_auditor_user_id
LEFT JOIN org_roles r ON r.id = a.lead_auditor_role_id
WHERE a.organization_id = ?
ORDER BY (a.planned_date IS NULL), a.planned_date DESC, a.id DESC',
[$orgId]
);
$out = array_map(static fn($a) => [
'id' => (int) $a['id'], 'code' => $a['code'], 'title' => $a['title'], 'status' => $a['status'],
'planned_date' => $a['planned_date'], 'executed_date' => $a['executed_date'],
'lead_auditor_user_id' => $a['lead_auditor_user_id'] !== null ? (int) $a['lead_auditor_user_id'] : null,
'lead_auditor_name' => $a['lead_auditor_name'],
'lead_auditor_role_id' => $a['lead_auditor_role_id'] !== null ? (int) $a['lead_auditor_role_id'] : null,
'lead_auditor_role_name' => $a['lead_auditor_role_name'],
'n_items' => (int) $a['n_items'], 'n_nc' => (int) $a['n_nc'], 'updated_at' => $a['updated_at'],
], $rows);
$this->jsonSuccess(['audits' => $out]);
}
/** GET /api/internal-audits/{id} (con items) */
public function get(int $id): void
{
$this->requireOrgAccess();
$orgId = $this->getCurrentOrgId();
$a = Database::fetchOne(
'SELECT a.*, u.full_name AS lead_auditor_name, r.role_name AS lead_auditor_role_name
FROM internal_audits a
LEFT JOIN users u ON u.id = a.lead_auditor_user_id
LEFT JOIN org_roles r ON r.id = a.lead_auditor_role_id
WHERE a.id = ? AND a.organization_id = ?',
[$id, $orgId]
);
if (!$a) { $this->jsonError('Audit non trovato', 404, 'NOT_FOUND'); }
$this->jsonSuccess([
'id' => (int) $a['id'], 'code' => $a['code'], 'title' => $a['title'],
'scope' => $a['scope'], 'criteria' => $a['criteria'],
'planned_date' => $a['planned_date'], 'executed_date' => $a['executed_date'],
'status' => $a['status'],
'lead_auditor_user_id' => $a['lead_auditor_user_id'] !== null ? (int) $a['lead_auditor_user_id'] : null,
'lead_auditor_name' => $a['lead_auditor_name'],
'lead_auditor_role_id' => $a['lead_auditor_role_id'] !== null ? (int) $a['lead_auditor_role_id'] : null,
'lead_auditor_role_name' => $a['lead_auditor_role_name'],
'conclusion' => $a['conclusion'], 'updated_at' => $a['updated_at'],
'items' => $this->loadItems($id),
]);
}
/**
* POST /api/internal-audits/create
* Genera code AUD-NNN progressivo per org e PRE-POPOLA la checklist:
* - clausole 4-10 ISO 27001 (statiche),
* - controlli Annex A applicabili dal SoA dell'org (se isms_soa esiste).
*/
public function create(): void
{
$this->requireOrgRole(self::MANAGE_ROLES);
$orgId = $this->getCurrentOrgId();
$b = $this->getJsonBody();
$title = trim((string) ($b['title'] ?? ''));
if ($title === '' || mb_strlen($title) > 255) { $this->jsonError('Titolo obbligatorio (max 255)', 422, 'INVALID_TITLE'); }
$status = in_array($b['status'] ?? '', ['planned', 'in_progress', 'completed', 'cancelled'], true) ? $b['status'] : 'planned';
$planned = $this->validateDate($b['planned_date'] ?? null, 'planned_date');
$executed = $this->validateDate($b['executed_date'] ?? null, 'executed_date');
$leadUserId = $this->validateUser($b['lead_auditor_user_id'] ?? null, $orgId);
$leadRoleId = $this->validateRole($b['lead_auditor_role_id'] ?? null, $orgId);
// Retry-on-duplicate: generateAuditCode() è check-poi-insert non atomico (race su
// create concorrenti); la UNIQUE (organization_id, code) [mig.058] blocca i doppioni
// e qui si rigenera il codice.
$base = [
'organization_id' => $orgId,
'title' => $title,
'scope' => $this->nullableStr($b['scope'] ?? null),
'criteria' => $this->nullableStr($b['criteria'] ?? null),
'planned_date' => $planned,
'executed_date' => $executed,
'status' => $status,
'lead_auditor_user_id' => $leadUserId,
'lead_auditor_role_id' => $leadRoleId,
'conclusion' => $this->nullableStr($b['conclusion'] ?? null),
'created_by' => $this->getCurrentUserId(),
];
$code = null; $id = 0;
for ($attempt = 0; ; $attempt++) {
$code = $this->generateAuditCode($orgId);
try {
$id = (int) Database::insert('internal_audits', ['code' => $code] + $base);
break;
} catch (\PDOException $e) {
if (($e->errorInfo[1] ?? 0) === 1062 && $attempt < 4) { continue; }
throw $e;
}
}
$seeded = $this->seedChecklist($id, $orgId);
$this->upsertCalendar($id, $orgId, $code, $title, $planned);
$this->logAudit('internal_audit_created', 'internal_audit', $id, ['code' => $code, 'title' => $title, 'items' => $seeded]);
$this->jsonSuccess(['id' => $id, 'code' => $code, 'items_seeded' => $seeded], 'Audit creato', 201);
}
/** PUT /api/internal-audits/{id} */
public function update(int $id): void
{
$this->requireOrgRole(self::MANAGE_ROLES);
$orgId = $this->getCurrentOrgId();
$b = $this->getJsonBody();
$a = Database::fetchOne('SELECT id, code, title, planned_date FROM internal_audits WHERE id = ? AND organization_id = ?', [$id, $orgId]);
if (!$a) { $this->jsonError('Audit non trovato', 404, 'NOT_FOUND'); }
$updates = [];
if ($this->hasParam('title')) {
$title = trim((string) ($b['title'] ?? ''));
if ($title === '' || mb_strlen($title) > 255) { $this->jsonError('Titolo non valido (max 255)', 422, 'INVALID_TITLE'); }
$updates['title'] = $title;
}
if ($this->hasParam('scope')) { $updates['scope'] = $this->nullableStr($b['scope'] ?? null); }
if ($this->hasParam('criteria')) { $updates['criteria'] = $this->nullableStr($b['criteria'] ?? null); }
if ($this->hasParam('planned_date')) { $updates['planned_date'] = $this->validateDate($b['planned_date'] ?? null, 'planned_date'); }
if ($this->hasParam('executed_date')) { $updates['executed_date'] = $this->validateDate($b['executed_date'] ?? null, 'executed_date'); }
if ($this->hasParam('status') && in_array($b['status'], ['planned', 'in_progress', 'completed', 'cancelled'], true)) { $updates['status'] = $b['status']; }
if ($this->hasParam('conclusion')) { $updates['conclusion'] = $this->nullableStr($b['conclusion'] ?? null); }
if ($this->hasParam('lead_auditor_user_id')) { $updates['lead_auditor_user_id'] = $this->validateUser($b['lead_auditor_user_id'] ?? null, $orgId); }
if ($this->hasParam('lead_auditor_role_id')) { $updates['lead_auditor_role_id'] = $this->validateRole($b['lead_auditor_role_id'] ?? null, $orgId); }
if (!empty($updates)) {
Database::update('internal_audits', $updates, 'id = ? AND organization_id = ?', [$id, $orgId]);
}
// riallinea il calendario
$title = $updates['title'] ?? $a['title'];
$planned = array_key_exists('planned_date', $updates) ? $updates['planned_date'] : $a['planned_date'];
$this->upsertCalendar($id, $orgId, $a['code'], $title, $planned);
$this->logAudit('internal_audit_updated', 'internal_audit', $id, array_keys($updates));
$this->jsonSuccess(['id' => $id, 'updated' => array_keys($updates)], 'Audit aggiornato');
}
/** DELETE /api/internal-audits/{id} (org_admin) */
public function delete(int $id): void
{
$this->requireOrgRole(['org_admin']);
$orgId = $this->getCurrentOrgId();
$a = Database::fetchOne('SELECT id FROM internal_audits WHERE id = ? AND organization_id = ?', [$id, $orgId]);
if (!$a) { $this->jsonError('Audit non trovato', 404, 'NOT_FOUND'); }
Database::delete('review_schedule', 'organization_id = ? AND entity_type = ? AND entity_id = ?', [$orgId, 'internal_audit', $id]);
Database::delete('internal_audits', 'id = ? AND organization_id = ?', [$id, $orgId]); // items in cascata
$this->logAudit('internal_audit_deleted', 'internal_audit', $id);
$this->jsonSuccess(null, 'Audit eliminato');
}
// ─────────────────────────────────────────────────────────────────────────
// VOCI DI CHECKLIST
// ─────────────────────────────────────────────────────────────────────────
/**
* PUT /api/internal-audits/items/{subId} Body: {result?, note?}
* Aggiorna esito/note di una voce, verificando che appartenga a un audit dell'org.
*/
public function updateItem(int $itemId): void
{
$this->requireOrgRole(self::MANAGE_ROLES);
$orgId = $this->getCurrentOrgId();
$item = $this->assertItem($itemId, $orgId);
$b = $this->getJsonBody();
$updates = [];
if ($this->hasParam('result')) {
$allowed = ['da_verificare', 'conforme', 'non_conforme', 'osservazione', 'opportunita', 'non_applicabile'];
if (!in_array($b['result'] ?? '', $allowed, true)) { $this->jsonError('Esito non valido', 422, 'INVALID_RESULT'); }
$updates['result'] = $b['result'];
}
if ($this->hasParam('note')) { $updates['note'] = $this->nullableStr($b['note'] ?? null); }
if ($this->hasParam('checkpoint')) {
$cp = trim((string) ($b['checkpoint'] ?? ''));
if ($cp === '') { $this->jsonError('Checkpoint non valido', 422, 'INVALID_CHECKPOINT'); }
$updates['checkpoint'] = mb_substr($cp, 0, 2000);
}
if (empty($updates)) { $this->jsonError('Nessun campo da aggiornare', 400, 'NO_UPDATES'); }
Database::update('internal_audit_items', $updates, 'id = ?', [$itemId]);
$this->logAudit('internal_audit_item_updated', 'internal_audit_item', $itemId, ['audit_id' => (int) $item['audit_id']] + array_fill_keys(array_keys($updates), 1));
$this->jsonSuccess(['id' => $itemId, 'updated' => array_keys($updates)], 'Voce aggiornata');
}
/**
* POST /api/internal-audits/items Body: {audit_id*, checkpoint*, ref_type?, ref_code?, note?, result?}
* Aggiunge una voce custom alla checklist (l'audit deve appartenere all'org).
*/
public function addItem(): void
{
$this->requireOrgRole(self::MANAGE_ROLES);
$orgId = $this->getCurrentOrgId();
$b = $this->getJsonBody();
$auditId = (int) ($b['audit_id'] ?? 0);
$this->assertAudit($auditId, $orgId);
$checkpoint = trim((string) ($b['checkpoint'] ?? ''));
if ($checkpoint === '') { $this->jsonError('Checkpoint obbligatorio', 422, 'INVALID_CHECKPOINT'); }
$refType = in_array($b['ref_type'] ?? '', ['clause', 'annex_control', 'nis2_measure', 'custom'], true) ? $b['ref_type'] : 'custom';
$result = in_array($b['result'] ?? '', ['da_verificare', 'conforme', 'non_conforme', 'osservazione', 'opportunita', 'non_applicabile'], true) ? $b['result'] : 'da_verificare';
$maxOrd = (int) (Database::fetchOne('SELECT COALESCE(MAX(ord), 0) AS m FROM internal_audit_items WHERE audit_id = ?', [$auditId])['m'] ?? 0);
$id = (int) Database::insert('internal_audit_items', [
'audit_id' => $auditId,
'ref_type' => $refType,
'ref_code' => $this->nullableStr($b['ref_code'] ?? null, 32),
'checkpoint' => mb_substr($checkpoint, 0, 2000),
'result' => $result,
'note' => $this->nullableStr($b['note'] ?? null),
'ord' => $maxOrd + 1,
]);
$this->logAudit('internal_audit_item_added', 'internal_audit_item', $id, ['audit_id' => $auditId]);
$this->jsonSuccess(['id' => $id], 'Voce aggiunta', 201);
}
// ─────────────────────────────────────────────────────────────────────────
// FINDING -> NON CONFORMITA'
// ─────────────────────────────────────────────────────────────────────────
/**
* POST /api/internal-audits/{id}/raiseNcr Body: {item_id?}
* Crea una NC (non_conformities) da una voce non conforme dell'audit.
* source='audit' (l'ENUM 004 NON ha 'internal_audit'),
* source_entity_type='internal_audit_item', source_entity_id=item_id.
* Idempotente: se esiste già una NC per quella voce la restituisce.
*/
public function raiseNcr(int $id): void
{
$this->requireOrgRole(self::MANAGE_ROLES);
$orgId = $this->getCurrentOrgId();
$audit = Database::fetchOne('SELECT id, code FROM internal_audits WHERE id = ? AND organization_id = ?', [$id, $orgId]);
if (!$audit) { $this->jsonError('Audit non trovato', 404, 'NOT_FOUND'); }
$b = $this->getJsonBody();
$itemId = (int) ($b['item_id'] ?? 0);
if ($itemId <= 0) { $this->jsonError('item_id obbligatorio', 422, 'MISSING_ITEM'); }
$item = Database::fetchOne('SELECT id, audit_id, ref_type, ref_code, checkpoint FROM internal_audit_items WHERE id = ? AND audit_id = ?', [$itemId, $id]);
if (!$item) { $this->jsonError('Voce non trovata in questo audit', 404, 'ITEM_NOT_FOUND'); }
// Idempotenza: una sola NC per voce
$existing = Database::fetchOne(
"SELECT id, ncr_code FROM non_conformities
WHERE organization_id = ? AND source = 'audit' AND source_entity_type = 'internal_audit_item' AND source_entity_id = ?",
[$orgId, $itemId]
);
if ($existing) {
$this->jsonSuccess(['id' => (int) $existing['id'], 'ncr_code' => $existing['ncr_code'], 'already' => true], 'Non conformità già aperta per questa voce');
}
$checkpoint = (string) $item['checkpoint'];
$refCode = $item['ref_code'] ? '[' . $item['ref_code'] . '] ' : '';
$titleBase = $refCode . $checkpoint;
$title = mb_strlen($titleBase) > 200 ? mb_substr($titleBase, 0, 197) . '...' : $titleBase;
if ($title === '') { $title = 'Non conformità da audit interno ' . $audit['code']; }
$ncrCode = $this->generateCode('NCR');
$ncrId = (int) Database::insert('non_conformities', [
'organization_id' => $orgId,
'ncr_code' => $ncrCode,
'title' => $title,
'description' => "Rilevata nell'audit interno {$audit['code']}" . ($item['ref_code'] ? " (rif. {$item['ref_code']})" : '') . ": {$checkpoint}",
'source' => 'audit',
'source_entity_type' => 'internal_audit_item',
'source_entity_id' => $itemId,
'severity' => 'minor',
'status' => 'open',
'identified_by' => $this->getCurrentUserId(),
]);
$this->logAudit('internal_audit_ncr_raised', 'non_conformity', $ncrId, ['ncr_code' => $ncrCode, 'audit_id' => $id, 'item_id' => $itemId]);
$this->jsonSuccess(['id' => $ncrId, 'ncr_code' => $ncrCode, 'already' => false], 'Non conformità creata', 201);
}
// ─────────────────────────────────────────────────────────────────────────
// REPORT
// ─────────────────────────────────────────────────────────────────────────
/** GET /api/internal-audits/{id}/report — HTML stampabile (no PDF lib). */
public function report(int $id): void
{
$this->requireOrgRole(['org_admin', 'compliance_manager', 'auditor', 'board_member']);
$orgId = $this->getCurrentOrgId();
$a = Database::fetchOne(
'SELECT a.*, u.full_name AS lead_auditor_name, r.role_name AS lead_auditor_role_name, o.name AS org_name
FROM internal_audits a
LEFT JOIN users u ON u.id = a.lead_auditor_user_id
LEFT JOIN org_roles r ON r.id = a.lead_auditor_role_id
LEFT JOIN organizations o ON o.id = a.organization_id
WHERE a.id = ? AND a.organization_id = ?',
[$id, $orgId]
);
if (!$a) { $this->jsonError('Audit non trovato', 404, 'NOT_FOUND'); }
$items = $this->loadItems($id);
header('Content-Type: text/html; charset=utf-8');
echo $this->renderReport($a, $items);
exit;
}
// ─────────────────────────────────────────────────────────────────────────
// HELPER
// ─────────────────────────────────────────────────────────────────────────
private function loadItems(int $auditId): array
{
$rows = Database::fetchAll(
'SELECT id, ref_type, ref_code, checkpoint, result, note, ord
FROM internal_audit_items WHERE audit_id = ?
ORDER BY FIELD(ref_type, \'clause\',\'annex_control\',\'nis2_measure\',\'custom\'), ord ASC, id ASC',
[$auditId]
);
// mappa item -> eventuale NC già aperta (per il pulsante "apri NC" del frontend)
$ncByItem = [];
$ncs = Database::fetchAll(
"SELECT source_entity_id, id, ncr_code FROM non_conformities
WHERE source = 'audit' AND source_entity_type = 'internal_audit_item'
AND source_entity_id IN (SELECT id FROM internal_audit_items WHERE audit_id = ?)",
[$auditId]
);
foreach ($ncs as $n) { $ncByItem[(int) $n['source_entity_id']] = ['id' => (int) $n['id'], 'ncr_code' => $n['ncr_code']]; }
return array_map(static function ($r) use ($ncByItem) {
$iid = (int) $r['id'];
return [
'id' => $iid, 'ref_type' => $r['ref_type'], 'ref_code' => $r['ref_code'],
'checkpoint' => $r['checkpoint'], 'result' => $r['result'], 'note' => $r['note'],
'ord' => (int) $r['ord'],
'ncr' => $ncByItem[$iid] ?? null,
];
}, $rows);
}
/** Genera code AUD-NNN progressivo per org (es. AUD-001). */
private function generateAuditCode(int $orgId): string
{
$n = (int) (Database::fetchOne(
"SELECT COUNT(*) AS c FROM internal_audits WHERE organization_id = ?",
[$orgId]
)['c'] ?? 0);
// evita collisione su uno UNIQUE eventuale futuro: incrementa finché libero
for ($i = $n + 1; $i < $n + 1000; $i++) {
$code = 'AUD-' . str_pad((string) $i, 3, '0', STR_PAD_LEFT);
$exists = Database::fetchOne('SELECT id FROM internal_audits WHERE organization_id = ? AND code = ?', [$orgId, $code]);
if (!$exists) { return $code; }
}
return 'AUD-' . str_pad((string) ($n + 1), 3, '0', STR_PAD_LEFT);
}
/**
* Pre-popola la checklist: clausole 4-10 (statiche) + Annex A applicabili da SoA.
* Ritorna il numero di voci create.
*/
private function seedChecklist(int $auditId, int $orgId): int
{
$ord = 0;
$count = 0;
foreach (self::ISO_CLAUSES as [$code, $checkpoint]) {
Database::insert('internal_audit_items', [
'audit_id' => $auditId,
'ref_type' => 'clause',
'ref_code' => $code,
'checkpoint' => $checkpoint,
'result' => 'da_verificare',
'ord' => $ord++,
]);
$count++;
}
// Annex A dal SoA dell'org (query difensiva: isms_soa potrebbe non esistere)
try {
$soa = Database::fetchAll(
"SELECT control_code, source_ref FROM isms_soa
WHERE organization_id = ? AND applicable = 1
ORDER BY control_code ASC",
[$orgId]
);
$ord = 0;
foreach ($soa as $s) {
$cp = $s['source_ref'] ? (string) $s['source_ref'] : ('Controllo applicabile (SoA): ' . $s['control_code']);
Database::insert('internal_audit_items', [
'audit_id' => $auditId,
'ref_type' => 'annex_control',
'ref_code' => mb_substr((string) $s['control_code'], 0, 32),
'checkpoint' => mb_substr($cp, 0, 2000),
'result' => 'da_verificare',
'ord' => $ord++,
]);
$count++;
}
} catch (PDOException $e) {
// tabella SoA assente o non popolata per l'org: la checklist resta con le sole clausole
error_log('[InternalAudit] SoA seed skipped: ' . $e->getMessage());
}
return $count;
}
/** Crea/aggiorna/elimina la riga di calendario (review_schedule) per l'audit. */
private function upsertCalendar(int $auditId, int $orgId, ?string $code, string $title, ?string $plannedDate): void
{
if ($plannedDate === null) {
Database::delete('review_schedule', 'organization_id = ? AND entity_type = ? AND entity_id = ?', [$orgId, 'internal_audit', $auditId]);
return;
}
$label = mb_substr('Audit interno ' . ($code ? $code . ': ' : '') . $title, 0, 255);
try {
Database::query(
'INSERT INTO review_schedule (organization_id, entity_type, entity_id, title, next_review_date, created_by)
VALUES (?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE title = VALUES(title), next_review_date = VALUES(next_review_date)',
[$orgId, 'internal_audit', $auditId, $label, $plannedDate, $this->getCurrentUserId()]
);
} catch (PDOException $e) {
// l'ENUM review_schedule.entity_type potrebbe non includere ancora 'internal_audit'
// (estensione lato seeder/flotta): non bloccare la creazione dell'audit.
error_log('[InternalAudit] calendar upsert skipped: ' . $e->getMessage());
}
}
private function assertAudit(int $id, int $orgId): array
{
$a = Database::fetchOne('SELECT id, code FROM internal_audits WHERE id = ? AND organization_id = ?', [$id, $orgId]);
if (!$a) { $this->jsonError('Audit non trovato', 404, 'NOT_FOUND'); }
return $a;
}
/** Verifica che la voce appartenga a un audit dell'org (anti-IDOR). */
private function assertItem(int $itemId, int $orgId): array
{
$item = Database::fetchOne(
'SELECT i.id, i.audit_id FROM internal_audit_items i
JOIN internal_audits a ON a.id = i.audit_id
WHERE i.id = ? AND a.organization_id = ?',
[$itemId, $orgId]
);
if (!$item) { $this->jsonError('Voce non trovata', 404, 'NOT_FOUND'); }
return $item;
}
private function validateUser($id, int $orgId): ?int
{
$id = ($id === null || $id === '') ? null : (int) $id;
if ($id === null) { return null; }
// l'utente deve essere membro dell'org (anti-IDOR)
$row = Database::fetchOne(
'SELECT u.id FROM users u
JOIN user_organizations uo ON uo.user_id = u.id
WHERE u.id = ? AND uo.organization_id = ?',
[$id, $orgId]
);
if (!$row) { $this->jsonError('Auditor capo non valido', 422, 'INVALID_AUDITOR'); }
return $id;
}
private function validateRole($id, int $orgId): ?int
{
$id = ($id === null || $id === '') ? null : (int) $id;
if ($id === null) { return null; }
$row = Database::fetchOne('SELECT id FROM org_roles WHERE id = ? AND organization_id = ?', [$id, $orgId]);
if (!$row) { $this->jsonError('Ruolo auditor non valido', 422, 'INVALID_ROLE'); }
return $id;
}
private function validateDate($v, string $field): ?string
{
if ($v === null || $v === '') { return null; }
$d = trim((string) $v);
$dt = DateTime::createFromFormat('Y-m-d', $d);
if (!$dt || $dt->format('Y-m-d') !== $d) { $this->jsonError("Data $field non valida (atteso AAAA-MM-GG)", 422, 'INVALID_DATE'); }
return $d;
}
private function nullableStr($v, ?int $max = null): ?string
{
if ($v === null) { return null; }
$s = trim((string) $v);
if ($s === '') { return null; }
if ($max !== null && mb_strlen($s) > $max) { $s = mb_substr($s, 0, $max); }
return $s;
}
/** Report HTML stampabile, self-contained (no PDF lib, no asset esterni). */
private function renderReport(array $a, array $items): string
{
$esc = static fn($s) => htmlspecialchars((string) ($s ?? ''), ENT_QUOTES, 'UTF-8');
$resLabels = [
'da_verificare' => 'Da verificare', 'conforme' => 'Conforme', 'non_conforme' => 'Non conforme',
'osservazione' => 'Osservazione', 'opportunita' => 'Opportunità', 'non_applicabile' => 'Non applicabile',
];
$resColors = [
'da_verificare' => '#6b7280', 'conforme' => '#166534', 'non_conforme' => '#991b1b',
'osservazione' => '#92400e', 'opportunita' => '#1e40af', 'non_applicabile' => '#6b7280',
];
$typeLabels = ['clause' => 'Clausole ISO 27001', 'annex_control' => 'Controlli Annex A', 'nis2_measure' => 'Misure NIS2', 'custom' => 'Voci aggiuntive'];
// raggruppa per ref_type mantenendo l'ordine
$groups = [];
foreach ($items as $it) { $groups[$it['ref_type']][] = $it; }
// conteggi esiti
$tally = [];
foreach ($items as $it) { $tally[$it['result']] = ($tally[$it['result']] ?? 0) + 1; }
$leadParts = [];
if (!empty($a['lead_auditor_name'])) { $leadParts[] = $a['lead_auditor_name']; }
if (!empty($a['lead_auditor_role_name'])) { $leadParts[] = '(' . $a['lead_auditor_role_name'] . ')'; }
$lead = $leadParts ? implode(' ', $leadParts) : '—';
$h = '<!DOCTYPE html><html lang="it"><head><meta charset="UTF-8">';
$h .= '<meta name="viewport" content="width=device-width, initial-scale=1">';
$h .= '<title>Report audit interno ' . $esc($a['code']) . '</title><style>';
$h .= 'body{font-family:-apple-system,Segoe UI,Roboto,Helvetica,Arial,sans-serif;color:#1f2937;max-width:900px;margin:24px auto;padding:0 20px;line-height:1.5;}';
$h .= 'h1{font-size:1.5rem;margin:0 0 4px;}h2{font-size:1.05rem;margin:26px 0 10px;border-bottom:2px solid #e5e7eb;padding-bottom:5px;}';
$h .= '.sub{color:#6b7280;font-size:.9rem;margin-bottom:18px;}';
$h .= '.meta{width:100%;border-collapse:collapse;font-size:.9rem;margin-bottom:8px;}';
$h .= '.meta th{text-align:left;width:170px;color:#6b7280;font-weight:600;vertical-align:top;padding:5px 10px 5px 0;}';
$h .= '.meta td{padding:5px 0;vertical-align:top;}';
$h .= 'table.cl{width:100%;border-collapse:collapse;font-size:.86rem;margin-bottom:10px;}';
$h .= 'table.cl th,table.cl td{text-align:left;padding:7px 9px;border:1px solid #e5e7eb;vertical-align:top;}';
$h .= 'table.cl th{background:#f9fafb;font-size:.72rem;text-transform:uppercase;letter-spacing:.03em;color:#6b7280;}';
$h .= '.pill{display:inline-block;font-size:.72rem;font-weight:700;padding:2px 8px;border-radius:6px;color:#fff;white-space:nowrap;}';
$h .= '.tally span{display:inline-block;margin-right:10px;font-size:.85rem;}';
$h .= '.print-btn{margin:0 0 18px;padding:8px 16px;border:1px solid #2563eb;background:#2563eb;color:#fff;border-radius:8px;cursor:pointer;font-size:.9rem;}';
$h .= '@media print{.print-btn{display:none;}body{margin:0;}}';
$h .= '</style></head><body>';
$h .= '<button class="print-btn" onclick="window.print()">Stampa / Salva PDF</button>';
$h .= '<h1>Report di audit interno</h1>';
$h .= '<div class="sub">' . $esc($a['org_name']) . ' &middot; ISO/IEC 27001 §9.2 &middot; generato il ' . date('d/m/Y H:i') . '</div>';
$h .= '<table class="meta">';
$h .= '<tr><th>Codice</th><td>' . $esc($a['code']) . '</td></tr>';
$h .= '<tr><th>Titolo</th><td>' . $esc($a['title']) . '</td></tr>';
$h .= '<tr><th>Stato</th><td>' . $esc($a['status']) . '</td></tr>';
$h .= '<tr><th>Auditor capo</th><td>' . $esc($lead) . '</td></tr>';
$h .= '<tr><th>Data pianificata</th><td>' . ($a['planned_date'] ? $esc(date('d/m/Y', strtotime($a['planned_date']))) : '—') . '</td></tr>';
$h .= '<tr><th>Data esecuzione</th><td>' . ($a['executed_date'] ? $esc(date('d/m/Y', strtotime($a['executed_date']))) : '—') . '</td></tr>';
$h .= '<tr><th>Ambito (scope)</th><td>' . nl2br($esc($a['scope'])) . '</td></tr>';
$h .= '<tr><th>Criteri</th><td>' . nl2br($esc($a['criteria'])) . '</td></tr>';
$h .= '</table>';
$h .= '<h2>Sintesi esiti</h2><div class="tally">';
foreach ($resLabels as $k => $lbl) {
$c = $tally[$k] ?? 0;
$h .= '<span><span class="pill" style="background:' . $resColors[$k] . '">' . $esc($lbl) . '</span> ' . $c . '</span>';
}
$h .= '</div>';
foreach ($typeLabels as $type => $label) {
if (empty($groups[$type])) { continue; }
$h .= '<h2>' . $esc($label) . '</h2>';
$h .= '<table class="cl"><thead><tr><th style="width:70px;">Rif.</th><th>Punto di verifica</th><th style="width:120px;">Esito</th><th>Note</th></tr></thead><tbody>';
foreach ($groups[$type] as $it) {
$col = $resColors[$it['result']] ?? '#6b7280';
$rl = $resLabels[$it['result']] ?? $it['result'];
$ncSuffix = $it['ncr'] ? ' <em style="color:#991b1b;font-size:.78rem;">NC ' . $esc($it['ncr']['ncr_code']) . '</em>' : '';
$h .= '<tr><td>' . $esc($it['ref_code']) . '</td><td>' . $esc($it['checkpoint']) . $ncSuffix . '</td>';
$h .= '<td><span class="pill" style="background:' . $col . '">' . $esc($rl) . '</span></td>';
$h .= '<td>' . nl2br($esc($it['note'])) . '</td></tr>';
}
$h .= '</tbody></table>';
}
if (!empty($a['conclusion'])) {
$h .= '<h2>Conclusioni</h2><p>' . nl2br($esc($a['conclusion'])) . '</p>';
}
$h .= '<p style="margin-top:30px;color:#9ca3af;font-size:.78rem;">Documento generato da NIS2 Agile. ISO/IEC 27001 §9.2 e\' una buona prassi volontaria; gli obblighi normativi in Italia derivano da NIS2 / D.Lgs. 138/2024. Strumento di supporto organizzativo, non un parere legale.</p>';
$h .= '</body></html>';
return $h;
}
}
Reference in New Issue View Git Blame Copy Permalink