barometer-server/handlers/results.php

<?php

$tokenRec = require_valid_token_web();

if ($method !== 'GET') {
    json_error('METHOD_NOT_ALLOWED', 'Method not allowed', 405);
}

$qnID       = $_GET['questionnaireID'] ?? '';
$clientCode = $_GET['clientCode']      ?? '';
$allVersions = !empty($_GET['allVersions']);

if (!$qnID) {
    json_error('MISSING_PARAM', 'questionnaireID query param required', 400);
}

try {
    $opened = qdb_open_read_or_fail();
    [$pdo, $tmpDb, $lockFp] = $opened;

    $qn = $pdo->prepare("SELECT * FROM questionnaire WHERE questionnaireID = :id");
$qn->execute([':id' => $qnID]);
$questionnaire = $qn->fetch(PDO::FETCH_ASSOC);
if (!$questionnaire) {
    qdb_discard($tmpDb, $lockFp);
    json_error('NOT_FOUND', 'Questionnaire not found', 404);
}

$qStmt = $pdo->prepare("
    SELECT questionID, defaultText, type, orderIndex, isRequired, configJson
    FROM question WHERE questionnaireID = :id ORDER BY orderIndex
");
$qStmt->execute([':id' => $qnID]);
$questions = $qStmt->fetchAll(PDO::FETCH_ASSOC);
$questionIDs = array_column($questions, 'questionID');

foreach ($questions as &$q) {
    $q['isRequired'] = (int)$q['isRequired'];
    $q['orderIndex'] = (int)$q['orderIndex'];
    $cfg = json_decode($q['configJson'] ?? '{}', true) ?: [];
    $q['questionKey'] = qdb_question_column_label($cfg, $q['defaultText'], $q['questionID'], $qnID);
    $ao = $pdo->prepare("SELECT answerOptionID, defaultText, points, orderIndex
                         FROM answer_option WHERE questionID = :qid ORDER BY orderIndex");
    $ao->execute([':qid' => $q['questionID']]);
    $q['answerOptions'] = $ao->fetchAll(PDO::FETCH_ASSOC);
    foreach ($q['answerOptions'] as &$opt) {
        $opt['points']     = (int)$opt['points'];
        $opt['orderIndex'] = (int)$opt['orderIndex'];
    }
}
unset($q);

[$rbacClause, $rbacParams] = rbac_client_filter($tokenRec, 'cl');

if ($allVersions) {
    require_once __DIR__ . '/../lib/submissions.php';
    $ctx = qdb_export_questionnaire_context($pdo, $qnID);
    $submissions = qdb_results_submission_entries(
        $pdo,
        $qnID,
        $ctx['questions'],
        $ctx['resultColumns'],
        $ctx['optionTextMap'],
        $tokenRec
    );
    if ($clientCode) {
        $submissions = array_values(array_filter(
            $submissions,
            static fn(array $row): bool => ($row['clientCode'] ?? '') === $clientCode
        ));
    }

    qdb_discard($tmpDb, $lockFp);

    json_success([
        'questionnaire' => $questionnaire,
        'questions'     => $questions,
        'allVersions'   => true,
        'submissions'   => $submissions,
    ]);
}

$sql = "
    SELECT cl.clientCode, cl.coachID,
           co.username AS coachUsername,
           sv.username AS supervisorUsername,
           cq.status, cq.startedAt, cq.completedAt, cq.sumPoints, cq.assignedByCoach,
           cq.programCycleNumber, cq.programSessionID, cq.programSessionNumber
    FROM client cl
    INNER JOIN completed_questionnaire cq ON cq.clientCode = cl.clientCode AND cq.questionnaireID = :qnid
    LEFT JOIN coach co ON co.coachID = cl.coachID
    LEFT JOIN supervisor sv ON sv.supervisorID = co.supervisorID
    WHERE $rbacClause
";
$params = array_merge([':qnid' => $qnID], $rbacParams);

if ($clientCode) {
    $sql .= " AND cl.clientCode = :cc";
    $params[':cc'] = $clientCode;
}
$sql .= " ORDER BY cl.clientCode";

$cStmt = $pdo->prepare($sql);
$cStmt->execute($params);
$clients = $cStmt->fetchAll(PDO::FETCH_ASSOC);

if (!empty($questionIDs) && !empty($clients)) {
    $qPlaceholders = implode(',', array_fill(0, count($questionIDs), '?'));
    $answerStmt = $pdo->prepare("
        SELECT clientCode, questionID, answerOptionID, freeTextValue, numericValue, answeredAt
        FROM client_answer
        WHERE clientCode = ? AND questionID IN ($qPlaceholders)
    ");

    foreach ($clients as &$c) {
        $c['sumPoints']   = $c['sumPoints'] !== null ? (int)$c['sumPoints'] : null;
        $c['startedAt']   = $c['startedAt'] !== null ? (int)$c['startedAt'] : null;
        $c['completedAt'] = $c['completedAt'] !== null ? (int)$c['completedAt'] : null;
        $c['programCycleNumber'] = $c['programCycleNumber'] !== null ? (int)$c['programCycleNumber'] : null;
        $c['programSessionNumber'] = $c['programSessionNumber'] !== null ? (int)$c['programSessionNumber'] : null;

        $bindParams = array_merge([$c['clientCode']], $questionIDs);
        $answerStmt->execute($bindParams);
        $answers = $answerStmt->fetchAll(PDO::FETCH_ASSOC);
        $answerMap = [];
        foreach ($answers as $a) {
            $answerMap[$a['questionID']] = [
                'answerOptionID' => $a['answerOptionID'],
                'freeTextValue'  => $a['freeTextValue'],
                'numericValue'   => $a['numericValue'] !== null ? (float)$a['numericValue'] : null,
                'answeredAt'     => $a['answeredAt'] !== null ? (int)$a['answeredAt'] : null,
            ];
        }
        $c['answers'] = $answerMap;
    }
    unset($c);
} else {
    foreach ($clients as &$c) {
        $c['sumPoints']   = $c['sumPoints'] !== null ? (int)$c['sumPoints'] : null;
        $c['startedAt']   = $c['startedAt'] !== null ? (int)$c['startedAt'] : null;
        $c['completedAt'] = $c['completedAt'] !== null ? (int)$c['completedAt'] : null;
        $c['programCycleNumber'] = $c['programCycleNumber'] !== null ? (int)$c['programCycleNumber'] : null;
        $c['programSessionNumber'] = $c['programSessionNumber'] !== null ? (int)$c['programSessionNumber'] : null;
        $c['answers'] = (object)[];
    }
    unset($c);
}

    qdb_discard($tmpDb, $lockFp);

    json_success([
        'questionnaire' => $questionnaire,
        'questions'     => $questions,
        'allVersions'   => false,
        'clients'       => $clients,
    ]);
} catch (Throwable $e) {
    qdb_handler_fail($e, 'Load results', null, $tmpDb ?? null, $lockFp ?? null);
}