948 lines
33 KiB
PHP
948 lines
33 KiB
PHP
<?php
|
||
/**
|
||
* Questionnaire scoring engine and scoring-profile aggregation.
|
||
*/
|
||
|
||
require_once __DIR__ . '/app_answers.php';
|
||
|
||
/** Default glass-scale level keys and implicit points (0–4). */
|
||
function qdb_glass_scale_level_keys(): array {
|
||
return ['never_glass', 'little_glass', 'moderate_glass', 'much_glass', 'extreme_glass'];
|
||
}
|
||
|
||
function qdb_default_glass_level_points(): array {
|
||
return [
|
||
'never_glass' => 0,
|
||
'little_glass' => 1,
|
||
'moderate_glass' => 2,
|
||
'much_glass' => 3,
|
||
'extreme_glass' => 4,
|
||
];
|
||
}
|
||
|
||
function qdb_make_score_rule_id(string $questionID, string $scopeKey, string $levelKey): string {
|
||
return substr(hash('sha256', $questionID . "\0" . $scopeKey . "\0" . $levelKey), 0, 32);
|
||
}
|
||
|
||
/**
|
||
* @return list<array{scopeKey: string, levelKey: string, points: int}>
|
||
*/
|
||
function qdb_get_score_rules_for_question(PDO $pdo, string $questionID): array {
|
||
$stmt = $pdo->prepare(
|
||
'SELECT scopeKey, levelKey, points FROM question_score_rule WHERE questionID = :qid ORDER BY scopeKey, levelKey'
|
||
);
|
||
$stmt->execute([':qid' => $questionID]);
|
||
$rows = [];
|
||
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
|
||
$rows[] = [
|
||
'scopeKey' => (string)($r['scopeKey'] ?? ''),
|
||
'levelKey' => (string)($r['levelKey'] ?? ''),
|
||
'points' => (int)($r['points'] ?? 0),
|
||
];
|
||
}
|
||
return $rows;
|
||
}
|
||
|
||
/**
|
||
* @param list<array{scopeKey?: string, levelKey: string, points: int}> $rules
|
||
*/
|
||
function qdb_sync_question_score_rules(PDO $pdo, string $questionID, array $rules): void {
|
||
$pdo->prepare('DELETE FROM question_score_rule WHERE questionID = :qid')
|
||
->execute([':qid' => $questionID]);
|
||
if ($rules === []) {
|
||
return;
|
||
}
|
||
$ins = $pdo->prepare(
|
||
'INSERT INTO question_score_rule (ruleID, questionID, scopeKey, levelKey, points)
|
||
VALUES (:rid, :qid, :sk, :lk, :pts)'
|
||
);
|
||
foreach ($rules as $rule) {
|
||
$scopeKey = trim((string)($rule['scopeKey'] ?? ''));
|
||
$levelKey = trim((string)($rule['levelKey'] ?? ''));
|
||
if ($levelKey === '') {
|
||
continue;
|
||
}
|
||
$ins->execute([
|
||
':rid' => qdb_make_score_rule_id($questionID, $scopeKey, $levelKey),
|
||
':qid' => $questionID,
|
||
':sk' => $scopeKey,
|
||
':lk' => $levelKey,
|
||
':pts' => (int)($rule['points'] ?? 0),
|
||
]);
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Build nested map: scopeKey -> levelKey -> points.
|
||
*
|
||
* @return array<string, array<string, int>>
|
||
*/
|
||
function qdb_score_rule_map_for_question(PDO $pdo, string $questionID): array {
|
||
$map = [];
|
||
foreach (qdb_get_score_rules_for_question($pdo, $questionID) as $rule) {
|
||
$map[$rule['scopeKey']][$rule['levelKey']] = $rule['points'];
|
||
}
|
||
return $map;
|
||
}
|
||
|
||
/**
|
||
* Attach scoreRules to glass symptom rows for editor API.
|
||
*
|
||
* @return list<array{key: string, labelDe: string, scoreLevels: array<string, int>}>
|
||
*/
|
||
function qdb_glass_symptoms_with_score_rules(PDO $pdo, string $questionID, array $config): array {
|
||
$ruleMap = $questionID !== '' ? qdb_score_rule_map_for_question($pdo, $questionID) : [];
|
||
$defaults = qdb_default_glass_level_points();
|
||
$rows = [];
|
||
foreach (qdb_glass_symptoms_with_labels($pdo, $config) as $row) {
|
||
$key = $row['key'];
|
||
$levels = [];
|
||
foreach (qdb_glass_scale_level_keys() as $levelKey) {
|
||
$levels[$levelKey] = $ruleMap[$key][$levelKey]
|
||
?? $ruleMap[''][$levelKey]
|
||
?? $defaults[$levelKey]
|
||
?? 0;
|
||
}
|
||
$rows[] = [
|
||
'key' => $key,
|
||
'labelDe' => $row['labelDe'],
|
||
'scoreLevels' => $levels,
|
||
];
|
||
}
|
||
return $rows;
|
||
}
|
||
|
||
/**
|
||
* Parse scoreRules from question save body.
|
||
*
|
||
* @return list<array{scopeKey: string, levelKey: string, points: int}>
|
||
*/
|
||
function qdb_parse_score_rules_body(array $body, string $questionType, array $config): array {
|
||
if (!isset($body['scoreRules']) || !is_array($body['scoreRules'])) {
|
||
if ($questionType === 'glass_scale_question' && isset($body['glassSymptoms']) && is_array($body['glassSymptoms'])) {
|
||
return qdb_score_rules_from_glass_symptoms($body['glassSymptoms']);
|
||
}
|
||
return [];
|
||
}
|
||
$rules = [];
|
||
foreach ($body['scoreRules'] as $row) {
|
||
if (!is_array($row)) {
|
||
continue;
|
||
}
|
||
$levelKey = trim((string)($row['levelKey'] ?? ''));
|
||
if ($levelKey === '') {
|
||
continue;
|
||
}
|
||
$rules[] = [
|
||
'scopeKey' => trim((string)($row['scopeKey'] ?? '')),
|
||
'levelKey' => $levelKey,
|
||
'points' => (int)($row['points'] ?? 0),
|
||
];
|
||
}
|
||
return $rules;
|
||
}
|
||
|
||
/**
|
||
* @param list<array{key?: string, scoreLevels?: array<string, int>}> $glassSymptoms
|
||
* @return list<array{scopeKey: string, levelKey: string, points: int}>
|
||
*/
|
||
function qdb_score_rules_from_glass_symptoms(array $glassSymptoms): array {
|
||
$rules = [];
|
||
$defaults = qdb_default_glass_level_points();
|
||
foreach ($glassSymptoms as $row) {
|
||
if (!is_array($row)) {
|
||
continue;
|
||
}
|
||
$scopeKey = trim((string)($row['key'] ?? ''));
|
||
if ($scopeKey === '') {
|
||
continue;
|
||
}
|
||
$levels = is_array($row['scoreLevels'] ?? null) ? $row['scoreLevels'] : $defaults;
|
||
foreach (qdb_glass_scale_level_keys() as $levelKey) {
|
||
$rules[] = [
|
||
'scopeKey' => $scopeKey,
|
||
'levelKey' => $levelKey,
|
||
'points' => (int)($levels[$levelKey] ?? $defaults[$levelKey] ?? 0),
|
||
];
|
||
}
|
||
}
|
||
return $rules;
|
||
}
|
||
|
||
function qdb_normalize_scoring_bands(array $row): array {
|
||
$greenMax = (int)($row['greenMax'] ?? 12);
|
||
$yellowMax = (int)($row['yellowMax'] ?? 36);
|
||
return [
|
||
'greenMin' => (int)($row['greenMin'] ?? 0),
|
||
'greenMax' => $greenMax,
|
||
'yellowMin' => (int)($row['yellowMin'] ?? ($greenMax + 1)),
|
||
'yellowMax' => $yellowMax,
|
||
'redMin' => (int)($row['redMin'] ?? ($yellowMax + 1)),
|
||
];
|
||
}
|
||
|
||
/**
|
||
* @return string|null Error message, or null if valid.
|
||
*/
|
||
function qdb_validate_scoring_bands(array $bands): ?string {
|
||
if ($bands['greenMin'] > $bands['greenMax']) {
|
||
return 'Green min must not exceed green max';
|
||
}
|
||
if ($bands['yellowMin'] > $bands['yellowMax']) {
|
||
return 'Yellow min must not exceed yellow max';
|
||
}
|
||
if ($bands['greenMax'] >= $bands['yellowMin']) {
|
||
return 'Green range must end before yellow range starts';
|
||
}
|
||
if ($bands['yellowMax'] >= $bands['redMin']) {
|
||
return 'Yellow range must end before red range starts';
|
||
}
|
||
return null;
|
||
}
|
||
|
||
/**
|
||
* @param array<string, int>|int $bandsOrGreenMax Band map or legacy greenMax
|
||
*/
|
||
function qdb_band_for_total(float $total, array|int $bandsOrGreenMax, ?int $yellowMax = null): string {
|
||
$bands = is_array($bandsOrGreenMax)
|
||
? qdb_normalize_scoring_bands($bandsOrGreenMax)
|
||
: qdb_normalize_scoring_bands(['greenMax' => $bandsOrGreenMax, 'yellowMax' => $yellowMax ?? 36]);
|
||
|
||
if ($total >= $bands['greenMin'] && $total <= $bands['greenMax']) {
|
||
return 'green';
|
||
}
|
||
if ($total >= $bands['yellowMin'] && $total <= $bands['yellowMax']) {
|
||
return 'yellow';
|
||
}
|
||
if ($total >= $bands['redMin']) {
|
||
return 'red';
|
||
}
|
||
if ($total < $bands['greenMin']) {
|
||
return 'green';
|
||
}
|
||
if ($total < $bands['yellowMin']) {
|
||
return 'yellow';
|
||
}
|
||
return 'red';
|
||
}
|
||
|
||
function qdb_parse_scoring_bands_body(array $body): array {
|
||
$greenMax = (int)($body['greenMax'] ?? 12);
|
||
$yellowMax = (int)($body['yellowMax'] ?? 36);
|
||
return qdb_normalize_scoring_bands([
|
||
'greenMin' => (int)($body['greenMin'] ?? 0),
|
||
'greenMax' => $greenMax,
|
||
'yellowMin' => (int)($body['yellowMin'] ?? ($greenMax + 1)),
|
||
'yellowMax' => $yellowMax,
|
||
'redMin' => (int)($body['redMin'] ?? ($yellowMax + 1)),
|
||
]);
|
||
}
|
||
|
||
function qdb_scoring_bands_to_api(array $row): array {
|
||
$bands = qdb_normalize_scoring_bands($row);
|
||
return $bands;
|
||
}
|
||
|
||
function qdb_compute_questionnaire_score(PDO $pdo, string $clientCode, string $questionnaireID): int {
|
||
$qStmt = $pdo->prepare(
|
||
'SELECT questionID, type, configJson FROM question WHERE questionnaireID = :qn ORDER BY orderIndex'
|
||
);
|
||
$qStmt->execute([':qn' => $questionnaireID]);
|
||
$questions = $qStmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
$aoStmt = $pdo->prepare(
|
||
'SELECT ao.questionID, ao.defaultText, ao.points
|
||
FROM answer_option ao
|
||
JOIN question q ON q.questionID = ao.questionID
|
||
WHERE q.questionnaireID = :qn'
|
||
);
|
||
$aoStmt->execute([':qn' => $questionnaireID]);
|
||
$optionPoints = [];
|
||
foreach ($aoStmt->fetchAll(PDO::FETCH_ASSOC) as $ao) {
|
||
$optionPoints[$ao['questionID']][$ao['defaultText']] = (int)$ao['points'];
|
||
}
|
||
|
||
$aStmt = $pdo->prepare(
|
||
'SELECT ca.questionID, ca.answerOptionID, ca.freeTextValue, ca.numericValue,
|
||
ao.defaultText AS optionKey
|
||
FROM client_answer ca
|
||
LEFT JOIN answer_option ao ON ao.answerOptionID = ca.answerOptionID
|
||
JOIN question q ON q.questionID = ca.questionID
|
||
WHERE ca.clientCode = :cc AND q.questionnaireID = :qn'
|
||
);
|
||
$aStmt->execute([':cc' => $clientCode, ':qn' => $questionnaireID]);
|
||
$answersByQuestion = [];
|
||
foreach ($aStmt->fetchAll(PDO::FETCH_ASSOC) as $a) {
|
||
$answersByQuestion[$a['questionID']] = $a;
|
||
}
|
||
|
||
$total = 0;
|
||
foreach ($questions as $qRow) {
|
||
$total += qdb_score_points_for_question(
|
||
$pdo,
|
||
$qRow,
|
||
$answersByQuestion[$qRow['questionID']] ?? null,
|
||
$optionPoints[$qRow['questionID']] ?? []
|
||
);
|
||
}
|
||
return $total;
|
||
}
|
||
|
||
function qdb_score_points_for_question(
|
||
PDO $pdo,
|
||
array $questionRow,
|
||
?array $answerRow,
|
||
array $optionPointsMap,
|
||
): int {
|
||
if ($answerRow === null) {
|
||
return 0;
|
||
}
|
||
$type = (string)($questionRow['type'] ?? '');
|
||
$questionID = (string)($questionRow['questionID'] ?? '');
|
||
$config = json_decode($questionRow['configJson'] ?? '{}', true) ?: [];
|
||
$ruleMap = qdb_score_rule_map_for_question($pdo, $questionID);
|
||
|
||
switch ($type) {
|
||
case 'radio_question':
|
||
case 'string_spinner':
|
||
$key = trim((string)($answerRow['optionKey'] ?? $answerRow['freeTextValue'] ?? ''));
|
||
return $key !== '' ? (int)($optionPointsMap[$key] ?? 0) : 0;
|
||
|
||
case 'multi_check_box_question':
|
||
$raw = trim((string)($answerRow['freeTextValue'] ?? ''));
|
||
if ($raw === '') {
|
||
return 0;
|
||
}
|
||
$sum = 0;
|
||
foreach (qdb_decode_multi_check_values($raw) as $mk) {
|
||
$sum += (int)($optionPointsMap[$mk] ?? 0);
|
||
}
|
||
return $sum;
|
||
|
||
case 'glass_scale_question':
|
||
$json = trim((string)($answerRow['freeTextValue'] ?? ''));
|
||
if ($json === '') {
|
||
return 0;
|
||
}
|
||
$decoded = json_decode($json, true);
|
||
if (!is_array($decoded)) {
|
||
return 0;
|
||
}
|
||
$defaults = qdb_default_glass_level_points();
|
||
$sum = 0;
|
||
foreach ($decoded as $symptomKey => $levelKey) {
|
||
$sk = trim((string)$symptomKey);
|
||
$lk = trim((string)$levelKey);
|
||
if ($sk === '' || $lk === '') {
|
||
continue;
|
||
}
|
||
$sum += (int)($ruleMap[$sk][$lk]
|
||
?? $ruleMap[''][$lk]
|
||
?? $defaults[$lk]
|
||
?? 0);
|
||
}
|
||
return $sum;
|
||
|
||
case 'slider_question':
|
||
case 'value_spinner':
|
||
$value = $answerRow['numericValue'];
|
||
if ($value === null || $value === '') {
|
||
$value = $answerRow['freeTextValue'];
|
||
}
|
||
if ($value === null || $value === '') {
|
||
return 0;
|
||
}
|
||
$levelKey = is_numeric($value)
|
||
? (string)(int)round((float)$value)
|
||
: trim((string)$value);
|
||
return (int)($ruleMap[''][$levelKey] ?? 0);
|
||
|
||
default:
|
||
return 0;
|
||
}
|
||
}
|
||
|
||
function qdb_recompute_profile_scores_for_client(PDO $pdo, string $clientCode, ?string $triggerQuestionnaireID = null): void {
|
||
$profileSql = 'SELECT profileID, name, greenMin, greenMax, yellowMin, yellowMax, redMin FROM scoring_profile WHERE isActive = 1';
|
||
$params = [];
|
||
if ($triggerQuestionnaireID !== null && $triggerQuestionnaireID !== '') {
|
||
$profileSql = '
|
||
SELECT sp.profileID, sp.name, sp.greenMin, sp.greenMax, sp.yellowMin, sp.yellowMax, sp.redMin
|
||
FROM scoring_profile sp
|
||
JOIN scoring_profile_questionnaire spq ON spq.profileID = sp.profileID
|
||
WHERE sp.isActive = 1 AND spq.questionnaireID = :qn';
|
||
$params[':qn'] = $triggerQuestionnaireID;
|
||
}
|
||
$pStmt = $pdo->prepare($profileSql);
|
||
$pStmt->execute($params);
|
||
$profiles = $pStmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
$deleteOrphan = $pdo->prepare(
|
||
'DELETE FROM client_scoring_profile_result WHERE clientCode = :cc AND profileID = :pid'
|
||
);
|
||
$upsert = $pdo->prepare(
|
||
'INSERT INTO client_scoring_profile_result
|
||
(clientCode, profileID, weightedTotal, band, computedAt, questionnaireSnapshot)
|
||
VALUES (:cc, :pid, :wt, :band, :at, :snap) '
|
||
. qdb_upsert_update(
|
||
['clientCode', 'profileID'],
|
||
[
|
||
'weightedTotal' => 'excluded.weightedTotal',
|
||
'band' => 'excluded.band',
|
||
'computedAt' => 'excluded.computedAt',
|
||
'questionnaireSnapshot' => 'excluded.questionnaireSnapshot',
|
||
]
|
||
)
|
||
);
|
||
|
||
foreach ($profiles as $profile) {
|
||
$profileID = $profile['profileID'];
|
||
$members = qdb_scoring_profile_members($pdo, $profileID);
|
||
if ($members === []) {
|
||
continue;
|
||
}
|
||
|
||
$snapshot = [];
|
||
$weightedTotal = 0.0;
|
||
$allComplete = true;
|
||
|
||
foreach ($members as $member) {
|
||
$qnID = $member['questionnaireID'];
|
||
$weight = (float)$member['weight'];
|
||
$cq = $pdo->prepare(
|
||
"SELECT status, sumPoints FROM completed_questionnaire
|
||
WHERE clientCode = :cc AND questionnaireID = :qn"
|
||
);
|
||
$cq->execute([':cc' => $clientCode, ':qn' => $qnID]);
|
||
$row = $cq->fetch(PDO::FETCH_ASSOC);
|
||
if (!$row || ($row['status'] ?? '') !== 'completed') {
|
||
$allComplete = false;
|
||
break;
|
||
}
|
||
$sumPoints = (int)($row['sumPoints'] ?? 0);
|
||
$contribution = $sumPoints * $weight;
|
||
$weightedTotal += $contribution;
|
||
$snapshot[$qnID] = [
|
||
'sumPoints' => $sumPoints,
|
||
'weight' => $weight,
|
||
'contribution' => round($contribution, 4),
|
||
];
|
||
}
|
||
|
||
if (!$allComplete) {
|
||
$deleteOrphan->execute([':cc' => $clientCode, ':pid' => $profileID]);
|
||
continue;
|
||
}
|
||
|
||
$band = qdb_band_for_total($weightedTotal, qdb_normalize_scoring_bands($profile));
|
||
$upsert->execute([
|
||
':cc' => $clientCode,
|
||
':pid' => $profileID,
|
||
':wt' => round($weightedTotal, 4),
|
||
':band' => $band,
|
||
':at' => time(),
|
||
':snap' => json_encode($snapshot, JSON_UNESCAPED_UNICODE),
|
||
]);
|
||
}
|
||
}
|
||
|
||
/**
|
||
* @return list<array{questionnaireID: string, weight: float, orderIndex: int, name?: string}>
|
||
*/
|
||
function qdb_scoring_profile_members(PDO $pdo, string $profileID): array {
|
||
$stmt = $pdo->prepare(
|
||
'SELECT spq.questionnaireID, spq.weight, spq.orderIndex, q.name
|
||
FROM scoring_profile_questionnaire spq
|
||
JOIN questionnaire q ON q.questionnaireID = spq.questionnaireID
|
||
WHERE spq.profileID = :pid
|
||
ORDER BY spq.orderIndex, spq.questionnaireID'
|
||
);
|
||
$stmt->execute([':pid' => $profileID]);
|
||
$rows = [];
|
||
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
|
||
$rows[] = [
|
||
'questionnaireID' => $r['questionnaireID'],
|
||
'weight' => (float)$r['weight'],
|
||
'orderIndex' => (int)$r['orderIndex'],
|
||
'name' => $r['name'] ?? '',
|
||
];
|
||
}
|
||
return $rows;
|
||
}
|
||
|
||
/**
|
||
* @return list<array<string, mixed>>
|
||
*/
|
||
function qdb_list_scoring_profiles(PDO $pdo): array {
|
||
$stmt = $pdo->query(
|
||
'SELECT profileID, name, description, isActive,
|
||
greenMin, greenMax, yellowMin, yellowMax, redMin,
|
||
processCompleteBands, createdAt, updatedAt
|
||
FROM scoring_profile ORDER BY name'
|
||
);
|
||
$profiles = [];
|
||
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
||
$row['isActive'] = (int)$row['isActive'];
|
||
$bands = qdb_normalize_scoring_bands($row);
|
||
$row = array_merge($row, $bands);
|
||
$row['processCompleteBands'] = qdb_row_process_complete_bands($row);
|
||
$row['questionnaires'] = qdb_scoring_profile_members($pdo, $row['profileID']);
|
||
$profiles[] = $row;
|
||
}
|
||
return $profiles;
|
||
}
|
||
|
||
function qdb_get_scoring_profile(PDO $pdo, string $profileID): ?array {
|
||
$stmt = $pdo->prepare(
|
||
'SELECT profileID, name, description, isActive,
|
||
greenMin, greenMax, yellowMin, yellowMax, redMin,
|
||
processCompleteBands, createdAt, updatedAt
|
||
FROM scoring_profile WHERE profileID = :pid'
|
||
);
|
||
$stmt->execute([':pid' => $profileID]);
|
||
$row = $stmt->fetch(PDO::FETCH_ASSOC);
|
||
if (!$row) {
|
||
return null;
|
||
}
|
||
$row['isActive'] = (int)$row['isActive'];
|
||
$row = array_merge($row, qdb_normalize_scoring_bands($row));
|
||
$row['processCompleteBands'] = qdb_row_process_complete_bands($row);
|
||
$row['questionnaires'] = qdb_scoring_profile_members($pdo, $profileID);
|
||
return $row;
|
||
}
|
||
|
||
/**
|
||
* Backfill default glass score rules (0–4) for all glass questions missing rules.
|
||
*/
|
||
function qdb_backfill_glass_score_rules(PDO $pdo): int {
|
||
$stmt = $pdo->query("SELECT questionID, configJson FROM question WHERE type = 'glass_scale_question'");
|
||
$count = 0;
|
||
$defaults = qdb_default_glass_level_points();
|
||
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
||
$existing = qdb_get_score_rules_for_question($pdo, $row['questionID']);
|
||
if ($existing !== []) {
|
||
continue;
|
||
}
|
||
$config = json_decode($row['configJson'] ?? '{}', true) ?: [];
|
||
$rules = [];
|
||
foreach ($config['symptoms'] ?? [] as $symptomKey) {
|
||
$sk = trim((string)$symptomKey);
|
||
if ($sk === '') {
|
||
continue;
|
||
}
|
||
foreach ($defaults as $levelKey => $pts) {
|
||
$rules[] = ['scopeKey' => $sk, 'levelKey' => $levelKey, 'points' => $pts];
|
||
}
|
||
}
|
||
if ($rules !== []) {
|
||
qdb_sync_question_score_rules($pdo, $row['questionID'], $rules);
|
||
$count++;
|
||
}
|
||
}
|
||
return $count;
|
||
}
|
||
|
||
/**
|
||
* Recompute questionnaire sumPoints and scoring-profile results for specific clients.
|
||
*
|
||
* @param list<string> $clientCodes
|
||
*/
|
||
function qdb_recompute_scores_for_clients(PDO $pdo, array $clientCodes): int {
|
||
$clientCodes = array_values(array_unique(array_filter(
|
||
$clientCodes,
|
||
static fn($cc) => is_string($cc) && trim($cc) !== ''
|
||
)));
|
||
if ($clientCodes === []) {
|
||
return 0;
|
||
}
|
||
|
||
$n = 0;
|
||
$qnStmt = $pdo->prepare(
|
||
"SELECT questionnaireID FROM completed_questionnaire
|
||
WHERE clientCode = :cc AND status = 'completed'"
|
||
);
|
||
$upd = $pdo->prepare(
|
||
'UPDATE completed_questionnaire SET sumPoints = :sp WHERE clientCode = :cc AND questionnaireID = :qn'
|
||
);
|
||
foreach ($clientCodes as $clientCode) {
|
||
$qnStmt->execute([':cc' => $clientCode]);
|
||
foreach ($qnStmt->fetchAll(PDO::FETCH_COLUMN) as $qnID) {
|
||
$score = qdb_compute_questionnaire_score($pdo, $clientCode, (string)$qnID);
|
||
$upd->execute([
|
||
':sp' => $score,
|
||
':cc' => $clientCode,
|
||
':qn' => $qnID,
|
||
]);
|
||
$n++;
|
||
}
|
||
qdb_recompute_profile_scores_for_client($pdo, $clientCode);
|
||
}
|
||
return $n;
|
||
}
|
||
|
||
function qdb_recompute_all_questionnaire_scores(PDO $pdo): int {
|
||
$stmt = $pdo->query(
|
||
"SELECT DISTINCT clientCode FROM completed_questionnaire WHERE status = 'completed'"
|
||
);
|
||
$clientCodes = $stmt->fetchAll(PDO::FETCH_COLUMN);
|
||
return qdb_recompute_scores_for_clients($pdo, $clientCodes);
|
||
}
|
||
|
||
function qdb_seed_default_rhs_scoring_profile(PDO $pdo): ?string {
|
||
foreach (qdb_list_scoring_profiles($pdo) as $p) {
|
||
if ($p['name'] === 'RHS Index') {
|
||
return $p['profileID'];
|
||
}
|
||
}
|
||
$rhsId = 'questionnaire_2_rhs';
|
||
$chk = $pdo->prepare('SELECT 1 FROM questionnaire WHERE questionnaireID = :id');
|
||
$chk->execute([':id' => $rhsId]);
|
||
if (!$chk->fetch()) {
|
||
return null;
|
||
}
|
||
$profileID = bin2hex(random_bytes(16));
|
||
$now = time();
|
||
$pdo->prepare(
|
||
'INSERT INTO scoring_profile (profileID, name, description, isActive,
|
||
greenMin, greenMax, yellowMin, yellowMax, redMin, processCompleteBands, createdAt, updatedAt)
|
||
VALUES (:id, :name, :desc, 1, 0, 12, 13, 36, 37, :pcb, :ca, :ua)'
|
||
)->execute([
|
||
':id' => $profileID,
|
||
':name' => 'RHS Index',
|
||
':desc' => 'Legacy RHS thresholds (green 0–12, yellow 13–36, red 37+)',
|
||
':pcb' => '[]',
|
||
':ca' => $now,
|
||
':ua' => $now,
|
||
]);
|
||
$pdo->prepare(
|
||
'INSERT INTO scoring_profile_questionnaire (profileID, questionnaireID, weight, orderIndex)
|
||
VALUES (:pid, :qn, 1.0, 0)'
|
||
)->execute([':pid' => $profileID, ':qn' => $rhsId]);
|
||
return $profileID;
|
||
}
|
||
|
||
function qdb_is_valid_scoring_band(?string $band): bool {
|
||
return in_array($band, ['green', 'yellow', 'red'], true);
|
||
}
|
||
|
||
/**
|
||
* @param mixed $raw
|
||
* @return list<string>
|
||
*/
|
||
function qdb_parse_process_complete_bands($raw): array {
|
||
if (is_string($raw)) {
|
||
$decoded = json_decode($raw, true);
|
||
$raw = is_array($decoded) ? $decoded : [];
|
||
}
|
||
if (!is_array($raw)) {
|
||
return [];
|
||
}
|
||
$out = [];
|
||
foreach ($raw as $band) {
|
||
$b = strtolower(trim((string)$band));
|
||
if (qdb_is_valid_scoring_band($b)) {
|
||
$out[] = $b;
|
||
}
|
||
}
|
||
return array_values(array_unique($out));
|
||
}
|
||
|
||
/**
|
||
* @return list<string>
|
||
*/
|
||
function qdb_decode_process_complete_bands(?string $json): array {
|
||
if ($json === null || trim($json) === '') {
|
||
return [];
|
||
}
|
||
return qdb_parse_process_complete_bands(json_decode($json, true));
|
||
}
|
||
|
||
/**
|
||
* @param list<string> $bands
|
||
*/
|
||
function qdb_encode_process_complete_bands(array $bands): string {
|
||
return json_encode(qdb_parse_process_complete_bands($bands), JSON_UNESCAPED_UNICODE);
|
||
}
|
||
|
||
/**
|
||
* @param array<string, mixed> $row
|
||
* @return list<string>
|
||
*/
|
||
function qdb_row_process_complete_bands(array $row): array {
|
||
if (array_key_exists('processCompleteBands', $row) && is_array($row['processCompleteBands'])) {
|
||
return qdb_parse_process_complete_bands($row['processCompleteBands']);
|
||
}
|
||
return qdb_decode_process_complete_bands((string)($row['processCompleteBands'] ?? '[]'));
|
||
}
|
||
|
||
/**
|
||
* Coach decision when set; otherwise the server-computed band.
|
||
*/
|
||
function qdb_effective_scoring_band(array $row): string {
|
||
$coach = trim((string)($row['coachBand'] ?? ''));
|
||
if ($coach !== '' && qdb_is_valid_scoring_band($coach)) {
|
||
return $coach;
|
||
}
|
||
return (string)($row['band'] ?? '');
|
||
}
|
||
|
||
function qdb_scoring_review_row_to_api(array $row, bool $includeComputed = true): array {
|
||
$coachBand = trim((string)($row['coachBand'] ?? ''));
|
||
$out = [
|
||
'profileID' => (string)($row['profileID'] ?? ''),
|
||
'name' => (string)($row['name'] ?? ''),
|
||
'coachBand' => $coachBand !== '' ? $coachBand : null,
|
||
'pendingReview' => $coachBand === '',
|
||
];
|
||
if ($includeComputed) {
|
||
$out['weightedTotal'] = (float)($row['weightedTotal'] ?? 0);
|
||
$out['calculatedBand'] = (string)($row['band'] ?? '');
|
||
$out['computedAt'] = !empty($row['computedAt']) ? date('Y-m-d H:i', (int)$row['computedAt']) : '';
|
||
}
|
||
return $out;
|
||
}
|
||
|
||
/**
|
||
* @param list<string> $clientCodes
|
||
* @return list<array{clientCode: string, profiles: list<array<string, mixed>>}>
|
||
*/
|
||
function qdb_app_scoring_review_for_clients(PDO $pdo, array $clientCodes): array {
|
||
if ($clientCodes === []) {
|
||
return [];
|
||
}
|
||
$placeholders = implode(',', array_fill(0, count($clientCodes), '?'));
|
||
$stmt = $pdo->prepare(
|
||
"SELECT r.clientCode, r.profileID, r.coachBand, sp.name
|
||
FROM client_scoring_profile_result r
|
||
JOIN scoring_profile sp ON sp.profileID = r.profileID AND sp.isActive = 1
|
||
WHERE r.clientCode IN ($placeholders)
|
||
ORDER BY r.clientCode, sp.name"
|
||
);
|
||
$stmt->execute(array_values($clientCodes));
|
||
$byClient = [];
|
||
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
||
$cc = (string)$row['clientCode'];
|
||
$byClient[$cc][] = qdb_scoring_review_row_to_api($row, false);
|
||
}
|
||
$out = [];
|
||
foreach ($clientCodes as $code) {
|
||
$out[] = [
|
||
'clientCode' => $code,
|
||
'profiles' => $byClient[$code] ?? [],
|
||
];
|
||
}
|
||
return $out;
|
||
}
|
||
|
||
/**
|
||
* @return array<string, mixed>
|
||
*/
|
||
/**
|
||
* Store coach band; create a result row from app-local computed values when none exists yet.
|
||
*
|
||
* @return array<string, mixed>
|
||
*/
|
||
function qdb_save_coach_scoring_review(
|
||
PDO $pdo,
|
||
string $clientCode,
|
||
string $profileID,
|
||
string $coachBand,
|
||
string $userID,
|
||
?float $weightedTotal = null,
|
||
?string $calculatedBand = null,
|
||
): array {
|
||
if (!qdb_is_valid_scoring_band($coachBand)) {
|
||
throw new InvalidArgumentException('coachBand must be green, yellow, or red');
|
||
}
|
||
|
||
$stmt = $pdo->prepare(
|
||
'SELECT r.band, r.coachBand, r.weightedTotal, r.computedAt, sp.name
|
||
FROM client_scoring_profile_result r
|
||
JOIN scoring_profile sp ON sp.profileID = r.profileID
|
||
WHERE r.clientCode = :cc AND r.profileID = :pid'
|
||
);
|
||
$stmt->execute([':cc' => $clientCode, ':pid' => $profileID]);
|
||
$row = $stmt->fetch(PDO::FETCH_ASSOC);
|
||
|
||
if (!$row) {
|
||
if ($weightedTotal === null || $calculatedBand === null || !qdb_is_valid_scoring_band($calculatedBand)) {
|
||
throw new RuntimeException('Scoring profile result not found; calculatedBand and weightedTotal required');
|
||
}
|
||
$profile = qdb_get_scoring_profile($pdo, $profileID);
|
||
if (!$profile || (int)($profile['isActive'] ?? 0) !== 1) {
|
||
throw new RuntimeException('Scoring profile not found');
|
||
}
|
||
$now = time();
|
||
$pdo->prepare(
|
||
'INSERT INTO client_scoring_profile_result
|
||
(clientCode, profileID, weightedTotal, band, computedAt, questionnaireSnapshot,
|
||
coachBand, coachReviewedAt, coachReviewedByUserID)
|
||
VALUES (:cc, :pid, :wt, :band, :at, :snap, :cb, :rat, :uid)'
|
||
)->execute([
|
||
':cc' => $clientCode,
|
||
':pid' => $profileID,
|
||
':wt' => round($weightedTotal, 4),
|
||
':band' => $calculatedBand,
|
||
':at' => $now,
|
||
':snap' => '{}',
|
||
':cb' => $coachBand,
|
||
':rat' => $now,
|
||
':uid' => $userID,
|
||
]);
|
||
return qdb_scoring_review_row_to_api([
|
||
'profileID' => $profileID,
|
||
'name' => $profile['name'],
|
||
'weightedTotal' => $weightedTotal,
|
||
'band' => $calculatedBand,
|
||
'coachBand' => $coachBand,
|
||
'computedAt' => $now,
|
||
]);
|
||
}
|
||
|
||
return qdb_set_coach_scoring_band($pdo, $clientCode, $profileID, $coachBand, $userID);
|
||
}
|
||
|
||
function qdb_set_coach_scoring_band(
|
||
PDO $pdo,
|
||
string $clientCode,
|
||
string $profileID,
|
||
string $coachBand,
|
||
string $userID
|
||
): array {
|
||
if (!qdb_is_valid_scoring_band($coachBand)) {
|
||
throw new InvalidArgumentException('coachBand must be green, yellow, or red');
|
||
}
|
||
$stmt = $pdo->prepare(
|
||
'SELECT r.band, r.coachBand, r.weightedTotal, r.computedAt, sp.name
|
||
FROM client_scoring_profile_result r
|
||
JOIN scoring_profile sp ON sp.profileID = r.profileID
|
||
WHERE r.clientCode = :cc AND r.profileID = :pid'
|
||
);
|
||
$stmt->execute([':cc' => $clientCode, ':pid' => $profileID]);
|
||
$row = $stmt->fetch(PDO::FETCH_ASSOC);
|
||
if (!$row) {
|
||
throw new RuntimeException('Scoring profile result not found or profile incomplete');
|
||
}
|
||
$now = time();
|
||
$pdo->prepare(
|
||
'UPDATE client_scoring_profile_result
|
||
SET coachBand = :cb, coachReviewedAt = :at, coachReviewedByUserID = :uid
|
||
WHERE clientCode = :cc AND profileID = :pid'
|
||
)->execute([
|
||
':cb' => $coachBand,
|
||
':at' => $now,
|
||
':uid' => $userID,
|
||
':cc' => $clientCode,
|
||
':pid' => $profileID,
|
||
]);
|
||
$row['coachBand'] = $coachBand;
|
||
$row['profileID'] = $profileID;
|
||
return qdb_scoring_review_row_to_api($row);
|
||
}
|
||
|
||
/**
|
||
* @return list<array{profileID: string, name: string}>
|
||
*/
|
||
function qdb_active_scoring_profiles(PDO $pdo): array {
|
||
$rows = $pdo->query(
|
||
"SELECT profileID, name FROM scoring_profile WHERE isActive = 1 ORDER BY name"
|
||
)->fetchAll(PDO::FETCH_ASSOC);
|
||
return array_map(static fn(array $p) => [
|
||
'profileID' => (string)$p['profileID'],
|
||
'name' => (string)$p['name'],
|
||
], $rows);
|
||
}
|
||
|
||
/**
|
||
* CSV / results table columns for active scoring profiles.
|
||
*
|
||
* @param list<array{profileID: string, name: string}> $profiles
|
||
* @return list<array{profileID: string, field: string, header: string}>
|
||
*/
|
||
function qdb_scoring_export_column_defs(array $profiles): array {
|
||
$cols = [];
|
||
foreach ($profiles as $p) {
|
||
$name = trim((string)($p['name'] ?? ''));
|
||
$prefix = $name !== '' ? $name : (string)($p['profileID'] ?? '');
|
||
$profileID = (string)($p['profileID'] ?? '');
|
||
if ($profileID === '') {
|
||
continue;
|
||
}
|
||
$cols[] = ['profileID' => $profileID, 'field' => 'calculated', 'header' => "{$prefix} calculated category"];
|
||
$cols[] = ['profileID' => $profileID, 'field' => 'counselor', 'header' => "{$prefix} counselor category"];
|
||
$cols[] = ['profileID' => $profileID, 'field' => 'override_by', 'header' => "{$prefix} override by"];
|
||
$cols[] = ['profileID' => $profileID, 'field' => 'override_at', 'header' => "{$prefix} override at"];
|
||
}
|
||
return $cols;
|
||
}
|
||
|
||
function qdb_scoring_export_cell_value(array $profileResult, string $field): string {
|
||
$calc = (string)($profileResult['band'] ?? '');
|
||
$coach = trim((string)($profileResult['coachBand'] ?? ''));
|
||
$differs = $coach !== '' && $coach !== $calc;
|
||
return match ($field) {
|
||
'calculated' => $calc,
|
||
'counselor' => $coach,
|
||
'override_by' => $differs ? (string)($profileResult['coachReviewedByUsername'] ?? '') : '',
|
||
'override_at' => $differs && !empty($profileResult['coachReviewedAt'])
|
||
? date('Y-m-d H:i', (int)$profileResult['coachReviewedAt']) : '',
|
||
default => '',
|
||
};
|
||
}
|
||
|
||
/**
|
||
* @param list<array{profileID: string, field: string, header: string}> $columnDefs
|
||
* @param array<string, array<string, mixed>> $byProfile profileID => result row
|
||
* @return array<string, string>
|
||
*/
|
||
function qdb_scoring_export_row_values(array $byProfile, array $columnDefs): array {
|
||
$row = [];
|
||
foreach ($columnDefs as $col) {
|
||
$profileID = (string)($col['profileID'] ?? '');
|
||
$result = $byProfile[$profileID] ?? null;
|
||
$row[$col['header']] = is_array($result)
|
||
? qdb_scoring_export_cell_value($result, (string)($col['field'] ?? ''))
|
||
: '';
|
||
}
|
||
return $row;
|
||
}
|
||
|
||
/**
|
||
* Scoring results for export, keyed by clientCode then profileID.
|
||
*
|
||
* @param list<string> $clientCodes
|
||
* @return array<string, array<string, array<string, mixed>>>
|
||
*/
|
||
function qdb_scoring_export_results_map(PDO $pdo, array $clientCodes, array $tokenRec): array {
|
||
if ($clientCodes === []) {
|
||
return [];
|
||
}
|
||
[$rbacClause, $rbacParams] = rbac_client_filter($tokenRec, 'cl');
|
||
$placeholders = implode(',', array_fill(0, count($clientCodes), '?'));
|
||
$stmt = $pdo->prepare(
|
||
"SELECT r.clientCode, r.profileID, r.band, r.coachBand, r.coachReviewedAt, r.coachReviewedByUserID,
|
||
u.username AS coachReviewedByUsername
|
||
FROM client_scoring_profile_result r
|
||
INNER JOIN scoring_profile sp ON sp.profileID = r.profileID AND sp.isActive = 1
|
||
INNER JOIN client cl ON cl.clientCode = r.clientCode
|
||
LEFT JOIN users u ON u.userID = r.coachReviewedByUserID
|
||
WHERE r.clientCode IN ($placeholders) AND ($rbacClause)"
|
||
);
|
||
$stmt->execute(array_merge(array_values($clientCodes), $rbacParams));
|
||
|
||
$map = [];
|
||
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
||
$cc = (string)$row['clientCode'];
|
||
$map[$cc][(string)$row['profileID']] = $row;
|
||
}
|
||
return $map;
|
||
}
|
||
|
||
/**
|
||
* @return list<string>
|
||
*/
|
||
function qdb_scoring_export_csv_headers(PDO $pdo): array {
|
||
$profiles = qdb_active_scoring_profiles($pdo);
|
||
return array_column(qdb_scoring_export_column_defs($profiles), 'header');
|
||
}
|