2528 lines
92 KiB
PHP
2528 lines
92 KiB
PHP
<?php
|
|
// /var/www/html/common.php
|
|
// Gemeinsame Helfer für Token-Validierung, Key-Derivation (HKDF) und AES (CBC, IV vorangestellt).
|
|
|
|
require_once __DIR__ . '/lib/text_sanitize.php';
|
|
|
|
/**
|
|
* Values loaded from the project .env file (see qdb_load_dotenv).
|
|
*
|
|
* @var array<string, string>
|
|
*/
|
|
$GLOBALS['qdb_dotenv'] = [];
|
|
|
|
function qdb_env_file_path(): string {
|
|
return __DIR__ . '/.env';
|
|
}
|
|
|
|
/**
|
|
* Parse a dotenv file. Does not use getenv/putenv.
|
|
* Not cached when unreadable (FPM workers can start before .env exists).
|
|
*
|
|
* @return array<string, string>
|
|
*/
|
|
function qdb_parse_env_file(string $path): array {
|
|
static $cache = [];
|
|
$mtime = @filemtime($path) ?: 0;
|
|
$cacheKey = $path . "\0" . $mtime;
|
|
if ($mtime > 0 && isset($cache[$cacheKey])) {
|
|
return $cache[$cacheKey];
|
|
}
|
|
$out = [];
|
|
if (!is_readable($path)) {
|
|
return $out;
|
|
}
|
|
$lines = file($path, FILE_IGNORE_NEW_LINES);
|
|
if ($lines === false) {
|
|
return $out;
|
|
}
|
|
foreach ($lines as $line) {
|
|
$line = trim($line);
|
|
if ($line === '' || $line[0] === '#') {
|
|
continue;
|
|
}
|
|
$eq = strpos($line, '=');
|
|
if ($eq === false) {
|
|
continue;
|
|
}
|
|
$name = trim(substr($line, 0, $eq));
|
|
$value = trim(substr($line, $eq + 1));
|
|
if ($name === '') {
|
|
continue;
|
|
}
|
|
$len = strlen($value);
|
|
if ($len >= 2) {
|
|
$q = $value[0];
|
|
if (($q === '"' && $value[$len - 1] === '"') || ($q === "'" && $value[$len - 1] === "'")) {
|
|
$value = substr($value, 1, -1);
|
|
}
|
|
}
|
|
$out[$name] = $value;
|
|
}
|
|
if ($mtime > 0) {
|
|
$cache[$cacheKey] = $out;
|
|
}
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* Read an environment variable (.env file first, then getenv / $_ENV / $_SERVER).
|
|
*/
|
|
function qdb_env_get(string $name): ?string {
|
|
$fileVars = qdb_parse_env_file(qdb_env_file_path());
|
|
if (isset($fileVars[$name]) && $fileVars[$name] !== '') {
|
|
return $fileVars[$name];
|
|
}
|
|
if (isset($GLOBALS['qdb_dotenv'][$name]) && $GLOBALS['qdb_dotenv'][$name] !== '') {
|
|
return $GLOBALS['qdb_dotenv'][$name];
|
|
}
|
|
// Do not use getenv() for QDB_MASTER_KEY — FPM can retain a stale system value
|
|
// when .env was missing on the worker's first request.
|
|
if ($name === 'QDB_MASTER_KEY') {
|
|
return null;
|
|
}
|
|
$v = getenv($name);
|
|
if ($v !== false && $v !== '') {
|
|
return $v;
|
|
}
|
|
if (isset($_ENV[$name]) && $_ENV[$name] !== '') {
|
|
return (string)$_ENV[$name];
|
|
}
|
|
if (isset($_SERVER[$name]) && $_SERVER[$name] !== '') {
|
|
return (string)$_SERVER[$name];
|
|
}
|
|
return null;
|
|
}
|
|
|
|
/** Set an environment variable for the current request (best-effort putenv). */
|
|
function qdb_env_set(string $name, string $value): void {
|
|
$_ENV[$name] = $value;
|
|
$_SERVER[$name] = $value;
|
|
if (function_exists('putenv')) {
|
|
@putenv("$name=$value");
|
|
}
|
|
}
|
|
|
|
/** Load KEY=value pairs from .env into $GLOBALS['qdb_dotenv'] and $_ENV. */
|
|
function qdb_load_dotenv(string $path): void {
|
|
foreach (qdb_parse_env_file($path) as $name => $value) {
|
|
$GLOBALS['qdb_dotenv'][$name] = $value;
|
|
qdb_env_set($name, $value);
|
|
}
|
|
}
|
|
|
|
qdb_load_dotenv(qdb_env_file_path());
|
|
|
|
// --- MASTER-KEY (Server-seitig zum Speichern der DB) ---
|
|
// Per ENV 'QDB_MASTER_KEY' (Base64- oder ASCII-String).
|
|
function get_master_key_bytes(): string {
|
|
$env = qdb_env_get('QDB_MASTER_KEY');
|
|
if ($env === null) {
|
|
$hint = is_readable(qdb_env_file_path()) ? 'missing QDB_MASTER_KEY in .env'
|
|
: '.env not readable by PHP (' . qdb_env_file_path() . ')';
|
|
throw new RuntimeException('QDB_MASTER_KEY is not set. ' . $hint);
|
|
}
|
|
$b = base64_decode($env, true);
|
|
if ($b !== false && strlen($b) > 0) {
|
|
return str_pad(substr($b, 0, 32), 32, "\0");
|
|
}
|
|
return str_pad(substr($env, 0, 32), 32, "\0");
|
|
}
|
|
|
|
// --- HKDF (SHA-256) über Token -> Session-Key (32 Byte) ---
|
|
// Kompatibel zu Android-Implementierung: salt = leer, info = "qdb-aes"
|
|
function hkdf_session_key_from_token(string $tokenHex, string $info = 'qdb-aes', int $len = 32): string {
|
|
$ikm = @hex2bin(trim($tokenHex));
|
|
if ($ikm === false) $ikm = $tokenHex; // Falls bereits binär
|
|
if (function_exists('hash_hkdf')) {
|
|
return hash_hkdf('sha256', $ikm, $len, $info, '');
|
|
}
|
|
// Fallback: eigene HKDF-Implementierung
|
|
$hashLen = 32;
|
|
$salt = str_repeat("\0", $hashLen);
|
|
$prk = hash_hmac('sha256', $ikm, $salt, true);
|
|
$okm = '';
|
|
$t = '';
|
|
$n = (int)ceil($len / $hashLen);
|
|
for ($i = 1; $i <= $n; $i++) {
|
|
$t = hash_hmac('sha256', $t . $info . chr($i), $prk, true);
|
|
$okm .= $t;
|
|
}
|
|
return substr($okm, 0, $len);
|
|
}
|
|
|
|
// --- Token-Storage (DB-backed via session table) ---
|
|
|
|
function token_storage_key(string $token): string {
|
|
return hash('sha256', $token);
|
|
}
|
|
|
|
function token_add(string $token, int $ttlSeconds = 86400, array $extra = []): void {
|
|
require_once __DIR__ . '/db_init.php';
|
|
[$pdo, $tmpDb, $lockFp] = qdb_open(true);
|
|
$now = time();
|
|
$pdo->prepare(
|
|
"INSERT INTO session (token, userID, role, entityID, createdAt, expiresAt, temp)
|
|
VALUES (:t, :uid, :role, :eid, :ca, :ea, :tmp)"
|
|
)->execute([
|
|
':t' => token_storage_key($token),
|
|
':uid' => $extra['userID'] ?? '',
|
|
':role' => $extra['role'] ?? '',
|
|
':eid' => $extra['entityID'] ?? '',
|
|
':ca' => $now,
|
|
':ea' => $now + $ttlSeconds,
|
|
':tmp' => (int)(!empty($extra['temp'])),
|
|
]);
|
|
// Opportunistic cleanup: remove expired sessions
|
|
$pdo->prepare("DELETE FROM session WHERE expiresAt < :now")->execute([':now' => $now]);
|
|
qdb_save($tmpDb, $lockFp);
|
|
}
|
|
|
|
function token_get_record(string $token, bool $refreshExpiry = true): ?array {
|
|
require_once __DIR__ . '/db_init.php';
|
|
require_once __DIR__ . '/lib/settings.php';
|
|
[$pdo, $tmpDb, $lockFp] = qdb_open($refreshExpiry);
|
|
$now = time();
|
|
$stmt = $pdo->prepare("SELECT * FROM session WHERE token = :t AND expiresAt >= :now");
|
|
$storedToken = token_storage_key($token);
|
|
$stmt->execute([':t' => $storedToken, ':now' => $now]);
|
|
$row = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
if (!$row) {
|
|
qdb_discard($tmpDb, $lockFp);
|
|
return null;
|
|
}
|
|
if ($refreshExpiry) {
|
|
$settings = qdb_settings_get_on_pdo($pdo);
|
|
$newExpiry = $now + qdb_session_ttl_seconds($settings, !empty($row['temp']));
|
|
$pdo->prepare('UPDATE session SET expiresAt = :exp WHERE token = :t')
|
|
->execute([':exp' => $newExpiry, ':t' => $storedToken]);
|
|
$row['expiresAt'] = $newExpiry;
|
|
qdb_save($tmpDb, $lockFp);
|
|
} else {
|
|
qdb_discard($tmpDb, $lockFp);
|
|
}
|
|
// Map DB columns to the keys the rest of the codebase expects
|
|
return [
|
|
'token' => $row['token'],
|
|
'role' => $row['role'],
|
|
'entityID' => $row['entityID'],
|
|
'userID' => $row['userID'],
|
|
'created' => (int)$row['createdAt'],
|
|
'exp' => (int)$row['expiresAt'],
|
|
'temp' => (int)$row['temp'],
|
|
];
|
|
}
|
|
|
|
function token_revoke(string $token): void {
|
|
require_once __DIR__ . '/db_init.php';
|
|
[$pdo, $tmpDb, $lockFp] = qdb_open(true);
|
|
$pdo->prepare("DELETE FROM session WHERE token = :t")->execute([':t' => token_storage_key($token)]);
|
|
qdb_save($tmpDb, $lockFp);
|
|
}
|
|
|
|
/** Revoke all sessions for one user on an open writable connection. */
|
|
function token_revoke_all_for_user_on_pdo(PDO $pdo, string $userID): int {
|
|
if ($userID === '') {
|
|
return 0;
|
|
}
|
|
$stmt = $pdo->prepare('DELETE FROM session WHERE userID = :uid');
|
|
$stmt->execute([':uid' => $userID]);
|
|
return $stmt->rowCount();
|
|
}
|
|
|
|
/** Revoke every login session for a user (after password change or admin reset). */
|
|
function token_revoke_all_for_user(string $userID): int {
|
|
if ($userID === '') {
|
|
return 0;
|
|
}
|
|
require_once __DIR__ . '/db_init.php';
|
|
[$pdo, $tmpDb, $lockFp] = qdb_open(true);
|
|
$removed = token_revoke_all_for_user_on_pdo($pdo, $userID);
|
|
qdb_save($tmpDb, $lockFp);
|
|
return $removed;
|
|
}
|
|
|
|
/** Revoke every stored session (all users and devices). */
|
|
function token_revoke_all_on_pdo(PDO $pdo): int
|
|
{
|
|
if (!qdb_table_exists($pdo, 'session')) {
|
|
return 0;
|
|
}
|
|
$count = (int)$pdo->query('SELECT COUNT(*) FROM session')->fetchColumn();
|
|
$pdo->exec('DELETE FROM session');
|
|
return $count;
|
|
}
|
|
|
|
function token_revoke_all(): int
|
|
{
|
|
require_once __DIR__ . '/db_init.php';
|
|
[$pdo, $tmpDb, $lockFp] = qdb_open(true);
|
|
$removed = token_revoke_all_on_pdo($pdo);
|
|
qdb_save($tmpDb, $lockFp);
|
|
return $removed;
|
|
}
|
|
|
|
// --- Auth helpers for endpoints ---
|
|
function get_bearer_token(): ?string {
|
|
$auth = $_SERVER['HTTP_AUTHORIZATION'] ?? ($_SERVER['Authorization'] ?? '');
|
|
if (stripos($auth, 'Bearer ') === 0) {
|
|
return substr($auth, 7);
|
|
}
|
|
return null;
|
|
}
|
|
|
|
function require_valid_token(): array {
|
|
$token = get_bearer_token();
|
|
if (!$token) {
|
|
json_error('UNAUTHORIZED', 'Missing Bearer token', 401);
|
|
}
|
|
$rec = token_get_record($token);
|
|
if (!$rec) {
|
|
json_error('UNAUTHORIZED', 'Invalid or expired token', 401);
|
|
}
|
|
if (!empty($rec['temp'])) {
|
|
json_error('PASSWORD_CHANGE_REQUIRED', 'Password change required before access', 403);
|
|
}
|
|
$rec['_token'] = $token;
|
|
return $rec;
|
|
}
|
|
|
|
function require_role(array $allowed, array $tokenRecord): void {
|
|
$role = $tokenRecord['role'] ?? '';
|
|
if (!in_array($role, $allowed, true)) {
|
|
json_error('FORBIDDEN', 'Insufficient permissions', 403);
|
|
}
|
|
}
|
|
|
|
/** Roles that may use the management website (not the mobile app). */
|
|
function qdb_web_roles(): array {
|
|
return ['admin', 'supervisor'];
|
|
}
|
|
|
|
/** True when the request originates from the management website (see website/js/api.js). */
|
|
function qdb_is_web_client_request(): bool {
|
|
$h = $_SERVER['HTTP_X_QDB_CLIENT'] ?? '';
|
|
return strtolower(trim($h)) === 'web';
|
|
}
|
|
|
|
/** Website or coordinator mobile app — not the counselor field app. */
|
|
function qdb_is_management_client_request(): bool {
|
|
$h = strtolower(trim($_SERVER['HTTP_X_QDB_CLIENT'] ?? ''));
|
|
return in_array($h, ['web', 'coordinator'], true);
|
|
}
|
|
|
|
/** Reject coach sign-in from management clients; coaches use the field app only. */
|
|
function qdb_reject_coach_web_login(string $role): void {
|
|
if ($role === 'coach' && qdb_is_management_client_request()) {
|
|
json_error(
|
|
'FORBIDDEN',
|
|
'Counselor accounts can only sign in through the mobile app.',
|
|
403
|
|
);
|
|
}
|
|
}
|
|
|
|
/** Valid bearer token plus admin/supervisor role (website API). */
|
|
function require_valid_token_web(): array {
|
|
$rec = require_valid_token();
|
|
require_role(qdb_web_roles(), $rec);
|
|
return $rec;
|
|
}
|
|
|
|
/**
|
|
* Build a SQL WHERE clause fragment that restricts client visibility by role.
|
|
* Returns [string $clause, array $params].
|
|
* $clientAlias is the table alias or name that has a coachID column (e.g. 'client').
|
|
*/
|
|
/**
|
|
* @param string $archiveMode active — non-archived only (default); archived — archived only; all — no archive filter
|
|
*/
|
|
function rbac_client_filter(
|
|
array $tokenRecord,
|
|
string $clientAlias = 'client',
|
|
string $archiveMode = 'active'
|
|
): array {
|
|
$role = $tokenRecord['role'] ?? '';
|
|
$entityID = $tokenRecord['entityID'] ?? '';
|
|
switch ($role) {
|
|
case 'admin':
|
|
$clause = '1=1';
|
|
$params = [];
|
|
break;
|
|
case 'supervisor':
|
|
$clause = "$clientAlias.coachID IN (SELECT coachID FROM coach WHERE supervisorID = :rbac_eid)";
|
|
$params = [':rbac_eid' => $entityID];
|
|
break;
|
|
case 'coach':
|
|
$clause = "$clientAlias.coachID = :rbac_eid";
|
|
$params = [':rbac_eid' => $entityID];
|
|
break;
|
|
default:
|
|
return ['0=1', []];
|
|
}
|
|
|
|
$archiveClause = match ($archiveMode) {
|
|
'archived' => "COALESCE($clientAlias.archived, 0) = 1",
|
|
'all' => '1=1',
|
|
default => "COALESCE($clientAlias.archived, 0) = 0",
|
|
};
|
|
|
|
return ["($clause) AND ($archiveClause)", $params];
|
|
}
|
|
|
|
// --- Translations: German (de) is the canonical source language ---
|
|
define('QDB_SOURCE_LANGUAGE', 'de');
|
|
|
|
function qdb_ensure_source_language(PDO $pdo): void {
|
|
$pdo->prepare("INSERT INTO language (languageCode, name) VALUES (:lc, :n)
|
|
ON CONFLICT(languageCode) DO NOTHING")
|
|
->execute([':lc' => QDB_SOURCE_LANGUAGE, ':n' => 'German']);
|
|
}
|
|
|
|
function qdb_upsert_source_translation(PDO $pdo, string $type, string $id, string $text): void {
|
|
qdb_put_translation($pdo, $type, $id, QDB_SOURCE_LANGUAGE, $text);
|
|
}
|
|
|
|
/** Catalog of global app UI string keys (LanguageManager). */
|
|
function qdb_app_string_catalog(bool $reload = false): array {
|
|
static $catalog = null;
|
|
if (!$reload && $catalog !== null) {
|
|
return $catalog;
|
|
}
|
|
$path = qdb_app_string_catalog_path();
|
|
if (!is_readable($path)) {
|
|
$catalog = ['keys' => [], 'germanDefaults' => []];
|
|
return $catalog;
|
|
}
|
|
$data = json_decode(file_get_contents($path), true);
|
|
$catalog = is_array($data) ? $data : ['keys' => [], 'germanDefaults' => []];
|
|
return $catalog;
|
|
}
|
|
|
|
function qdb_app_string_catalog_path(): string {
|
|
return __DIR__ . '/data/app_ui_strings.json';
|
|
}
|
|
|
|
/** Remove questionnaire_group_* from catalog file (keys list + germanDefaults). */
|
|
function qdb_remove_questionnaire_group_from_catalog(string $stringKey): void {
|
|
if (!qdb_is_questionnaire_group_string_key($stringKey)) {
|
|
return;
|
|
}
|
|
$path = qdb_app_string_catalog_path();
|
|
if (!is_readable($path) || !is_writable($path)) {
|
|
return;
|
|
}
|
|
$data = json_decode(file_get_contents($path), true);
|
|
if (!is_array($data)) {
|
|
return;
|
|
}
|
|
$changed = false;
|
|
if (!empty($data['keys']) && is_array($data['keys'])) {
|
|
$next = array_values(array_filter(
|
|
$data['keys'],
|
|
fn($k) => (string)$k !== $stringKey
|
|
));
|
|
if (count($next) !== count($data['keys'])) {
|
|
$data['keys'] = $next;
|
|
$changed = true;
|
|
}
|
|
}
|
|
if (!empty($data['germanDefaults']) && is_array($data['germanDefaults']) && array_key_exists($stringKey, $data['germanDefaults'])) {
|
|
unset($data['germanDefaults'][$stringKey]);
|
|
$changed = true;
|
|
}
|
|
if (!$changed) {
|
|
return;
|
|
}
|
|
file_put_contents(
|
|
$path,
|
|
json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . "\n",
|
|
LOCK_EX
|
|
);
|
|
qdb_app_string_catalog(true);
|
|
}
|
|
|
|
function qdb_app_string_key_set(): array {
|
|
$keys = qdb_app_string_catalog()['keys'] ?? [];
|
|
return array_flip($keys);
|
|
}
|
|
|
|
/** Keys for dev-only UI (database export, etc.) — excluded from website App UI and app sync catalog. */
|
|
function qdb_dev_only_app_string_key_set(): array {
|
|
static $set = null;
|
|
if ($set !== null) {
|
|
return $set;
|
|
}
|
|
$set = array_flip([
|
|
'database',
|
|
'database_clients_title',
|
|
'download_header',
|
|
'export_success_downloads',
|
|
'export_failed',
|
|
'headers',
|
|
'view_missing',
|
|
'no_header_template_found',
|
|
'saved_pdf_csv',
|
|
'no_pdf_viewer',
|
|
'save_error',
|
|
'no_clients_available',
|
|
'questionnaire_id',
|
|
'questionnaires',
|
|
'status',
|
|
'data',
|
|
'data_final_warning',
|
|
'open_client_via_load',
|
|
'ask_before_upload',
|
|
'You have completed questionnaires that have not been uploaded yet.',
|
|
'You have completed questionnaires that have not been uploaded yet. Tap to open the app and upload.',
|
|
]);
|
|
return $set;
|
|
}
|
|
|
|
/** Prefix for app UI strings that label questionnaire category groups on the opening screen. */
|
|
function qdb_questionnaire_group_prefix(): string {
|
|
return 'questionnaire_group_';
|
|
}
|
|
|
|
function qdb_questionnaire_group_string_key(string $categoryKey): string {
|
|
$categoryKey = trim($categoryKey);
|
|
return $categoryKey === '' ? '' : qdb_questionnaire_group_prefix() . $categoryKey;
|
|
}
|
|
|
|
function qdb_is_questionnaire_group_string_key(string $stringKey): bool {
|
|
return str_starts_with($stringKey, qdb_questionnaire_group_prefix());
|
|
}
|
|
|
|
function qdb_category_key_from_group_string_key(string $stringKey): string {
|
|
$prefix = qdb_questionnaire_group_prefix();
|
|
return str_starts_with($stringKey, $prefix) ? substr($stringKey, strlen($prefix)) : '';
|
|
}
|
|
|
|
/** @return array<string, int> categoryKey => questionnaire count */
|
|
function qdb_category_keys_in_use(PDO $pdo): array {
|
|
$map = [];
|
|
$rows = $pdo->query(
|
|
"SELECT categoryKey, COUNT(*) AS c FROM questionnaire
|
|
WHERE trim(categoryKey) != '' GROUP BY categoryKey"
|
|
)->fetchAll(PDO::FETCH_ASSOC);
|
|
foreach ($rows as $row) {
|
|
$k = trim((string)($row['categoryKey'] ?? ''));
|
|
if ($k !== '') {
|
|
$map[$k] = (int)$row['c'];
|
|
}
|
|
}
|
|
return $map;
|
|
}
|
|
|
|
/**
|
|
* App string entries for category keys in use (not already in the static JSON catalog keys list).
|
|
* @return list<array{key: string, displayKey: string, type: string, entityId: string, sortOrder: int, germanDefault: string, categoryKey: string}>
|
|
*/
|
|
function qdb_category_group_string_entries(PDO $pdo): array {
|
|
$catalog = qdb_app_string_catalog();
|
|
$defaults = $catalog['germanDefaults'] ?? [];
|
|
$staticKeys = qdb_app_string_key_set();
|
|
$entries = [];
|
|
$order = 0;
|
|
foreach (qdb_category_keys_in_use($pdo) as $catKey => $_count) {
|
|
$stringKey = qdb_questionnaire_group_string_key($catKey);
|
|
if ($stringKey === '' || isset($staticKeys[$stringKey])) {
|
|
continue;
|
|
}
|
|
$entries[] = [
|
|
'key' => $stringKey,
|
|
'displayKey' => $stringKey,
|
|
'type' => 'app_string',
|
|
'entityId' => $stringKey,
|
|
'sortOrder' => $order++,
|
|
'germanDefault' => $defaults[$stringKey] ?? $catKey,
|
|
'categoryKey' => $catKey,
|
|
];
|
|
}
|
|
usort($entries, fn($a, $b) => strcmp($a['categoryKey'], $b['categoryKey']));
|
|
return $entries;
|
|
}
|
|
|
|
/**
|
|
* Category keys with translations in DB but no questionnaire using them.
|
|
* @return list<string>
|
|
*/
|
|
function qdb_orphaned_category_keys(PDO $pdo): array {
|
|
$inUse = qdb_category_keys_in_use($pdo);
|
|
$prefix = qdb_questionnaire_group_prefix();
|
|
$orphans = [];
|
|
$stmt = $pdo->query(
|
|
"SELECT DISTINCT stringKey FROM string_translation WHERE stringKey LIKE " . $pdo->quote($prefix . '%')
|
|
);
|
|
foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $stringKey) {
|
|
$catKey = qdb_category_key_from_group_string_key((string)$stringKey);
|
|
if ($catKey !== '' && !isset($inUse[$catKey])) {
|
|
$orphans[$catKey] = true;
|
|
}
|
|
}
|
|
ksort($orphans);
|
|
return array_keys($orphans);
|
|
}
|
|
|
|
/**
|
|
* @return list<array{categoryKey: string, stringKey: string, questionnaireCount: int, orphaned: bool, germanLabel: string}>
|
|
*/
|
|
function qdb_list_category_keys_for_dashboard(PDO $pdo): array {
|
|
$defaults = qdb_app_string_catalog()['germanDefaults'] ?? [];
|
|
$inUse = qdb_category_keys_in_use($pdo);
|
|
$list = [];
|
|
|
|
$loadGerman = function (string $stringKey) use ($pdo, $defaults): string {
|
|
$stmt = $pdo->prepare(
|
|
'SELECT text FROM string_translation WHERE stringKey = :k AND languageCode = :lang LIMIT 1'
|
|
);
|
|
$stmt->execute([':k' => $stringKey, ':lang' => QDB_SOURCE_LANGUAGE]);
|
|
$text = $stmt->fetchColumn();
|
|
if (is_string($text) && $text !== '') {
|
|
return $text;
|
|
}
|
|
return $defaults[$stringKey] ?? '';
|
|
};
|
|
|
|
foreach ($inUse as $catKey => $count) {
|
|
$stringKey = qdb_questionnaire_group_string_key($catKey);
|
|
$list[] = [
|
|
'categoryKey' => $catKey,
|
|
'stringKey' => $stringKey,
|
|
'questionnaireCount' => $count,
|
|
'orphaned' => false,
|
|
'germanLabel' => $loadGerman($stringKey) ?: $catKey,
|
|
];
|
|
}
|
|
|
|
foreach (qdb_orphaned_category_keys($pdo) as $catKey) {
|
|
$stringKey = qdb_questionnaire_group_string_key($catKey);
|
|
$list[] = [
|
|
'categoryKey' => $catKey,
|
|
'stringKey' => $stringKey,
|
|
'questionnaireCount' => 0,
|
|
'orphaned' => true,
|
|
'germanLabel' => $loadGerman($stringKey) ?: $catKey,
|
|
];
|
|
}
|
|
|
|
usort($list, fn($a, $b) => strcmp($a['categoryKey'], $b['categoryKey']));
|
|
return $list;
|
|
}
|
|
|
|
/** Persist default German for a questionnaire_group_* key in app_ui_strings.json. */
|
|
function qdb_update_questionnaire_group_german_default(string $stringKey, string $text): void {
|
|
if (!qdb_is_questionnaire_group_string_key($stringKey)) {
|
|
return;
|
|
}
|
|
$path = qdb_app_string_catalog_path();
|
|
if (!is_readable($path) || !is_writable($path)) {
|
|
return;
|
|
}
|
|
$data = json_decode(file_get_contents($path), true);
|
|
if (!is_array($data)) {
|
|
return;
|
|
}
|
|
if (!isset($data['germanDefaults']) || !is_array($data['germanDefaults'])) {
|
|
$data['germanDefaults'] = [];
|
|
}
|
|
if (($data['germanDefaults'][$stringKey] ?? '') === $text) {
|
|
return;
|
|
}
|
|
$data['germanDefaults'][$stringKey] = $text;
|
|
file_put_contents(
|
|
$path,
|
|
json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . "\n",
|
|
LOCK_EX
|
|
);
|
|
qdb_app_string_catalog(true);
|
|
}
|
|
|
|
/** Set German label for a category (app opening screen group title). */
|
|
function qdb_set_category_german_label(PDO $pdo, string $categoryKey, string $text): string {
|
|
$categoryKey = trim($categoryKey);
|
|
if ($categoryKey === '') {
|
|
json_error('INVALID_FIELD', 'categoryKey is required', 400);
|
|
}
|
|
$text = trim($text);
|
|
if ($text === '') {
|
|
json_error('INVALID_FIELD', 'German label is required', 400);
|
|
}
|
|
$stringKey = qdb_questionnaire_group_string_key($categoryKey);
|
|
qdb_set_app_string_german_label($pdo, $stringKey, $text);
|
|
qdb_update_questionnaire_group_german_default($stringKey, $text);
|
|
return $stringKey;
|
|
}
|
|
|
|
/**
|
|
* Remove a category key completely: clear questionnaires, translations, and catalog defaults.
|
|
* @return int questionnaires updated
|
|
*/
|
|
function qdb_delete_category_key(PDO $pdo, string $categoryKey): int {
|
|
$categoryKey = trim($categoryKey);
|
|
if ($categoryKey === '') {
|
|
json_error('INVALID_FIELD', 'categoryKey is required', 400);
|
|
}
|
|
$stringKey = qdb_questionnaire_group_string_key($categoryKey);
|
|
|
|
$stmt = $pdo->prepare('UPDATE questionnaire SET categoryKey = \'\' WHERE categoryKey = :ck');
|
|
$stmt->execute([':ck' => $categoryKey]);
|
|
$cleared = $stmt->rowCount();
|
|
|
|
$pdo->prepare('DELETE FROM string_translation WHERE stringKey = :k')
|
|
->execute([':k' => $stringKey]);
|
|
|
|
qdb_remove_questionnaire_group_from_catalog($stringKey);
|
|
|
|
return $cleared;
|
|
}
|
|
|
|
/**
|
|
* Global app UI strings (screens, toasts, auth, etc.) for the mobile app.
|
|
* @return list<array{key: string, type: string, entityId: string, sortOrder: int}>
|
|
*/
|
|
function qdb_app_ui_string_entries(): array {
|
|
$catalog = qdb_app_string_catalog();
|
|
$defaults = $catalog['germanDefaults'] ?? [];
|
|
$entries = [];
|
|
$order = 0;
|
|
foreach ($catalog['keys'] ?? [] as $key) {
|
|
if (qdb_is_questionnaire_group_string_key($key)) {
|
|
continue;
|
|
}
|
|
$entries[] = [
|
|
'key' => $key,
|
|
'displayKey' => $key,
|
|
'type' => 'app_string',
|
|
'entityId' => $key,
|
|
'sortOrder' => $order++,
|
|
'germanDefault' => $defaults[$key] ?? $key,
|
|
];
|
|
}
|
|
return $entries;
|
|
}
|
|
|
|
/**
|
|
* Static catalog plus questionnaire_group_* keys for categoryKey values in use.
|
|
* @return list<array{key: string, type: string, entityId: string, sortOrder: int}>
|
|
*/
|
|
/** messageKey field inside questionnaire conditionJson (app locked hint). */
|
|
function qdb_message_key_from_condition_json(?string $json): string {
|
|
if ($json === null || trim($json) === '' || trim($json) === '{}') {
|
|
return '';
|
|
}
|
|
$obj = json_decode($json, true);
|
|
if (!is_array($obj)) {
|
|
return '';
|
|
}
|
|
$key = trim((string)($obj['messageKey'] ?? ''));
|
|
return $key !== '' && qdb_is_stable_key($key) ? $key : '';
|
|
}
|
|
|
|
/** @return array<string, true> */
|
|
function qdb_collect_condition_message_keys(PDO $pdo): array {
|
|
$keys = [];
|
|
foreach ($pdo->query('SELECT conditionJson FROM questionnaire')->fetchAll(PDO::FETCH_COLUMN) as $json) {
|
|
$mk = qdb_message_key_from_condition_json($json);
|
|
if ($mk !== '') {
|
|
$keys[$mk] = true;
|
|
}
|
|
}
|
|
return $keys;
|
|
}
|
|
|
|
/**
|
|
* App string entries for condition messageKey values referenced in questionnaire conditions.
|
|
* @return list<array{key: string, displayKey: string, type: string, entityId: string, sortOrder: int, germanDefault: string}>
|
|
*/
|
|
function qdb_condition_message_string_entries(PDO $pdo): array {
|
|
$staticKeys = qdb_app_string_key_set();
|
|
$defaults = qdb_app_string_catalog()['germanDefaults'] ?? [];
|
|
$entries = [];
|
|
$order = 0;
|
|
foreach (array_keys(qdb_collect_condition_message_keys($pdo)) as $key) {
|
|
if (isset($staticKeys[$key])) {
|
|
continue;
|
|
}
|
|
$entries[] = [
|
|
'key' => $key,
|
|
'displayKey' => $key,
|
|
'type' => 'app_string',
|
|
'entityId' => $key,
|
|
'sortOrder' => $order++,
|
|
'germanDefault' => $defaults[$key] ?? $key,
|
|
];
|
|
}
|
|
usort($entries, fn($a, $b) => strcmp($a['key'], $b['key']));
|
|
return $entries;
|
|
}
|
|
|
|
function qdb_update_app_string_german_default(string $stringKey, string $text): void {
|
|
$path = qdb_app_string_catalog_path();
|
|
if (!is_readable($path) || !is_writable($path)) {
|
|
return;
|
|
}
|
|
$data = json_decode(file_get_contents($path), true);
|
|
if (!is_array($data)) {
|
|
return;
|
|
}
|
|
if (!isset($data['germanDefaults']) || !is_array($data['germanDefaults'])) {
|
|
$data['germanDefaults'] = [];
|
|
}
|
|
if (($data['germanDefaults'][$stringKey] ?? '') === $text) {
|
|
return;
|
|
}
|
|
$data['germanDefaults'][$stringKey] = $text;
|
|
file_put_contents(
|
|
$path,
|
|
json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . "\n",
|
|
LOCK_EX
|
|
);
|
|
qdb_app_string_catalog(true);
|
|
}
|
|
|
|
function qdb_set_app_string_german_label(PDO $pdo, string $stringKey, string $text): void {
|
|
$stringKey = qdb_validate_stable_key($stringKey, 'messageKey');
|
|
$text = trim($text);
|
|
if ($text === '') {
|
|
json_error('INVALID_FIELD', 'German label is required', 400);
|
|
}
|
|
qdb_put_translation($pdo, 'app_string', $stringKey, QDB_SOURCE_LANGUAGE, $text);
|
|
qdb_update_app_string_german_default($stringKey, $text);
|
|
}
|
|
|
|
function qdb_app_ui_string_entries_merged(PDO $pdo): array {
|
|
$merged = qdb_app_ui_string_entries();
|
|
$existing = [];
|
|
foreach ($merged as $e) {
|
|
$existing[$e['key']] = true;
|
|
}
|
|
$order = count($merged);
|
|
foreach (qdb_category_group_string_entries($pdo) as $e) {
|
|
if (!isset($existing[$e['key']])) {
|
|
$e['sortOrder'] = $order++;
|
|
$merged[] = $e;
|
|
$existing[$e['key']] = true;
|
|
}
|
|
}
|
|
foreach (qdb_condition_message_string_entries($pdo) as $e) {
|
|
if (!isset($existing[$e['key']])) {
|
|
$e['sortOrder'] = $order++;
|
|
$merged[] = $e;
|
|
$existing[$e['key']] = true;
|
|
}
|
|
}
|
|
return $merged;
|
|
}
|
|
|
|
/**
|
|
* All string keys referenced by questionnaire content (questions, options, config).
|
|
* @return array<string, true>
|
|
*/
|
|
function qdb_all_questionnaire_translation_key_set(PDO $pdo): array {
|
|
static $cache = null;
|
|
if ($cache !== null) {
|
|
return $cache;
|
|
}
|
|
|
|
$keys = [];
|
|
$rows = $pdo->query("SELECT defaultText, configJson FROM question")->fetchAll(PDO::FETCH_ASSOC);
|
|
foreach ($rows as $dbQ) {
|
|
$config = json_decode($dbQ['configJson'] ?: '{}', true) ?: [];
|
|
$qk = qdb_question_key($config, $dbQ['defaultText']);
|
|
if ($qk !== '') {
|
|
$keys[$qk] = true;
|
|
if (!empty($config['noteBefore'])) {
|
|
$keys[qdb_note_before_key($qk)] = true;
|
|
}
|
|
if (!empty($config['noteAfter'])) {
|
|
$keys[qdb_note_after_key($qk)] = true;
|
|
}
|
|
} elseif ($dbQ['defaultText'] !== '') {
|
|
$keys[$dbQ['defaultText']] = true;
|
|
}
|
|
foreach (['textKey', 'textKey1', 'textKey2', 'hint', 'hint1', 'hint2'] as $field) {
|
|
if (!empty($config[$field])) {
|
|
$keys[$config[$field]] = true;
|
|
}
|
|
}
|
|
if (!empty($config['symptoms']) && is_array($config['symptoms'])) {
|
|
foreach ($config['symptoms'] as $s) {
|
|
if ($s !== '') {
|
|
$keys[$s] = true;
|
|
}
|
|
}
|
|
}
|
|
if (!empty($config['options']) && is_array($config['options'])) {
|
|
foreach ($config['options'] as $opt) {
|
|
if (is_string($opt) && $opt !== '') {
|
|
$keys[$opt] = true;
|
|
} elseif (is_array($opt) && !empty($opt['key'])) {
|
|
$keys[$opt['key']] = true;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
foreach ($pdo->query("SELECT defaultText FROM answer_option")->fetchAll(PDO::FETCH_COLUMN) as $text) {
|
|
if ($text !== '') {
|
|
$keys[$text] = true;
|
|
}
|
|
}
|
|
|
|
$cache = $keys;
|
|
return $cache;
|
|
}
|
|
|
|
/**
|
|
* App UI catalog for the website Translations page (coach-visible, no dev-only keys).
|
|
* Catalog keys always appear here even when also used inside a questionnaire (e.g. "save").
|
|
*/
|
|
function qdb_app_ui_string_entries_for_website(PDO $pdo): array {
|
|
$dev = qdb_dev_only_app_string_key_set();
|
|
$entries = [];
|
|
foreach (qdb_app_ui_string_entries_merged($pdo) as $e) {
|
|
if (!isset($dev[$e['key']])) {
|
|
$entries[] = $e;
|
|
}
|
|
}
|
|
return $entries;
|
|
}
|
|
|
|
/** Upsert one translation row; sync German text into defaultText for questions/options. */
|
|
function qdb_put_translation(PDO $pdo, string $type, string $id, string $lang, string $text): void {
|
|
if ($type === 'question') {
|
|
$pdo->prepare("INSERT INTO question_translation (questionID, languageCode, text)
|
|
VALUES (:id, :lang, :t)
|
|
ON CONFLICT(questionID, languageCode) DO UPDATE SET text = excluded.text")
|
|
->execute([':id' => $id, ':lang' => $lang, ':t' => $text]);
|
|
if ($lang === QDB_SOURCE_LANGUAGE) {
|
|
$pdo->prepare("UPDATE question SET defaultText = :t WHERE questionID = :id")
|
|
->execute([':t' => $text, ':id' => $id]);
|
|
}
|
|
} elseif ($type === 'answer_option') {
|
|
$pdo->prepare("INSERT INTO answer_option_translation (answerOptionID, languageCode, text)
|
|
VALUES (:id, :lang, :t)
|
|
ON CONFLICT(answerOptionID, languageCode) DO UPDATE SET text = excluded.text")
|
|
->execute([':id' => $id, ':lang' => $lang, ':t' => $text]);
|
|
// defaultText holds optionKey; German label lives only in answer_option_translation.
|
|
} elseif ($type === 'string' || $type === 'app_string') {
|
|
$pdo->prepare("INSERT INTO string_translation (stringKey, languageCode, text)
|
|
VALUES (:id, :lang, :t)
|
|
ON CONFLICT(stringKey, languageCode) DO UPDATE SET text = excluded.text")
|
|
->execute([':id' => $id, ':lang' => $lang, ':t' => $text]);
|
|
}
|
|
}
|
|
|
|
/** Short label for translation UI (Key column); full text stays in `key` for API lookup. */
|
|
function qdb_translation_display_key(array $e): string {
|
|
return qdb_translation_row_label($e);
|
|
}
|
|
|
|
function qdb_short_entity_label(string $entityId): string {
|
|
$pos = strrpos($entityId, '__');
|
|
return $pos !== false ? substr($entityId, $pos + 2) : $entityId;
|
|
}
|
|
|
|
function qdb_require_non_empty_german(string $text, string $label = 'German text'): void {
|
|
if ($text === '') {
|
|
json_error('MISSING_FIELDS', "$label is required", 400);
|
|
}
|
|
}
|
|
|
|
/** Stable identifier: snake_case starting with a letter (case preserved for LM keys). */
|
|
function qdb_is_stable_key(string $s): bool {
|
|
return $s !== '' && (bool)preg_match('/^[a-zA-Z][a-zA-Z0-9_]*$/', $s);
|
|
}
|
|
|
|
/**
|
|
* Coerce an arbitrary key string into a stable identifier (letters, digits, underscores;
|
|
* must start with a letter). Used on import and API writes so bundles are not limited to
|
|
* pre-validated keys (e.g. numeric option labels "1", "42").
|
|
*/
|
|
function qdb_normalize_stable_key(string $s): string {
|
|
$s = trim($s);
|
|
if ($s === '') {
|
|
return '';
|
|
}
|
|
if (qdb_is_stable_key($s)) {
|
|
return $s;
|
|
}
|
|
$out = preg_replace('/[^a-zA-Z0-9_]+/', '_', $s);
|
|
$out = preg_replace('/_+/', '_', $out ?? '');
|
|
$out = trim($out, '_');
|
|
if ($out === '') {
|
|
return 'k_' . substr(hash('crc32b', $s), 0, 8);
|
|
}
|
|
if (!preg_match('/^[a-zA-Z]/', $out)) {
|
|
$out = 'k_' . $out;
|
|
}
|
|
return qdb_is_stable_key($out) ? $out : 'k_' . substr(hash('crc32b', $s), 0, 8);
|
|
}
|
|
|
|
function qdb_validate_stable_key(string $s, string $label = 'Key'): string {
|
|
$s = trim($s);
|
|
if ($s === '') {
|
|
json_error('INVALID_FIELD', "$label is required", 400);
|
|
}
|
|
$normalized = qdb_normalize_stable_key($s);
|
|
if (!qdb_is_stable_key($normalized)) {
|
|
json_error('INVALID_FIELD', "$label could not be normalized to a valid key", 400);
|
|
}
|
|
return $normalized;
|
|
}
|
|
|
|
/** Resolve app lookup key for a question row. */
|
|
function qdb_question_key(array $config, string $defaultText): string {
|
|
$key = trim((string)($config['questionKey'] ?? ''));
|
|
if ($key !== '' && qdb_is_stable_key($key)) {
|
|
return $key;
|
|
}
|
|
if (qdb_is_stable_key($defaultText)) {
|
|
return $defaultText;
|
|
}
|
|
return '';
|
|
}
|
|
|
|
/** Option key is stored in answer_option.defaultText. */
|
|
function qdb_option_key(array $aoRow): string {
|
|
$dt = trim((string)($aoRow['defaultText'] ?? ''));
|
|
return qdb_is_stable_key($dt) ? $dt : '';
|
|
}
|
|
|
|
/** Human-readable text for glass-scale answers stored as JSON on the parent question row. */
|
|
function qdb_format_glass_answer_json(?string $raw): string
|
|
{
|
|
if ($raw === null || trim($raw) === '') {
|
|
return '';
|
|
}
|
|
$decoded = json_decode($raw, true);
|
|
if (!is_array($decoded)) {
|
|
return $raw;
|
|
}
|
|
$parts = [];
|
|
foreach ($decoded as $key => $val) {
|
|
$val = trim((string)$val);
|
|
if ($val === '') {
|
|
continue;
|
|
}
|
|
$parts[] = $key . ': ' . $val;
|
|
}
|
|
return implode('; ', $parts);
|
|
}
|
|
|
|
/**
|
|
* Map glass symptom string keys to parent questionID for one questionnaire.
|
|
*
|
|
* @return array<string, string> symptomKey => parentQuestionID
|
|
*/
|
|
function qdb_glass_symptom_parent_map(PDO $pdo, string $qnID): array
|
|
{
|
|
$stmt = $pdo->prepare(
|
|
"SELECT questionID, configJson FROM question WHERE questionnaireID = :qn AND type = 'glass_scale_question'"
|
|
);
|
|
$stmt->execute([':qn' => $qnID]);
|
|
$map = [];
|
|
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
|
$cfg = json_decode($row['configJson'] ?? '{}', true) ?: [];
|
|
foreach ($cfg['symptoms'] ?? [] as $symptomKey) {
|
|
$sk = trim((string)$symptomKey);
|
|
if ($sk !== '') {
|
|
$map[$sk] = $row['questionID'];
|
|
}
|
|
}
|
|
}
|
|
return $map;
|
|
}
|
|
|
|
/** Merge symptom => label selections into JSON for one glass-scale parent question. */
|
|
function qdb_merge_glass_symptom_json(?string $existingJson, array $symptomLabels): string
|
|
{
|
|
$data = [];
|
|
if ($existingJson !== null && trim($existingJson) !== '') {
|
|
$decoded = json_decode($existingJson, true);
|
|
if (is_array($decoded)) {
|
|
$data = $decoded;
|
|
}
|
|
}
|
|
foreach ($symptomLabels as $key => $label) {
|
|
$k = trim((string)$key);
|
|
$v = trim((string)$label);
|
|
if ($k !== '' && $v !== '') {
|
|
$data[$k] = $v;
|
|
}
|
|
}
|
|
return json_encode($data, JSON_UNESCAPED_UNICODE);
|
|
}
|
|
|
|
/** Single symptom value from glass-scale JSON stored on the parent question row. */
|
|
function qdb_glass_symptom_answer_value(?string $json, string $symptomKey): string
|
|
{
|
|
$symptomKey = trim($symptomKey);
|
|
if ($symptomKey === '' || $json === null || trim($json) === '') {
|
|
return '';
|
|
}
|
|
$decoded = json_decode($json, true);
|
|
if (!is_array($decoded)) {
|
|
return '';
|
|
}
|
|
return trim((string)($decoded[$symptomKey] ?? ''));
|
|
}
|
|
|
|
/**
|
|
* Question types that exist only for app/editor flow (no answer payload to persist).
|
|
*/
|
|
function qdb_is_non_stored_answer_question_type(string $type): bool
|
|
{
|
|
return $type === 'last_page';
|
|
}
|
|
|
|
/**
|
|
* Flat columns for results / CSV: one column per glass-scale symptom.
|
|
*
|
|
* @return list<array{header: string, questionID: string, symptomKey: ?string, kind: string, question: array}>
|
|
*/
|
|
function qdb_results_export_columns(array $questions, string $qnID): array
|
|
{
|
|
$columns = [];
|
|
foreach ($questions as $q) {
|
|
$cfg = json_decode($q['configJson'] ?? '{}', true) ?: [];
|
|
$type = $q['type'] ?? '';
|
|
if (qdb_is_non_stored_answer_question_type($type)) {
|
|
continue;
|
|
}
|
|
if ($type === 'glass_scale_question') {
|
|
$symptoms = $cfg['symptoms'] ?? [];
|
|
if (is_array($symptoms) && $symptoms !== []) {
|
|
foreach ($symptoms as $symptomKey) {
|
|
$sk = trim((string)$symptomKey);
|
|
if ($sk === '') {
|
|
continue;
|
|
}
|
|
$columns[] = [
|
|
'header' => $sk,
|
|
'questionID' => $q['questionID'],
|
|
'symptomKey' => $sk,
|
|
'kind' => 'glass_symptom',
|
|
'question' => $q,
|
|
];
|
|
}
|
|
continue;
|
|
}
|
|
}
|
|
$columns[] = [
|
|
'header' => qdb_question_column_label($cfg, $q['defaultText'], $q['questionID'], $qnID),
|
|
'questionID' => $q['questionID'],
|
|
'symptomKey' => null,
|
|
'kind' => 'question',
|
|
'question' => $q,
|
|
];
|
|
}
|
|
return $columns;
|
|
}
|
|
|
|
/** Cell value for one export/results column (including glass symptom columns). */
|
|
function qdb_results_column_cell_value(array $column, ?array $answerRow, array $optionTextMap): string
|
|
{
|
|
if (($column['kind'] ?? '') === 'glass_symptom') {
|
|
return qdb_glass_symptom_answer_value(
|
|
$answerRow['freeTextValue'] ?? null,
|
|
(string)($column['symptomKey'] ?? '')
|
|
);
|
|
}
|
|
return qdb_format_client_answer_display($column['question'], $answerRow, $optionTextMap);
|
|
}
|
|
|
|
/** Format one client_answer row for results table / CSV export. */
|
|
function qdb_format_client_answer_display(array $question, ?array $answerRow, array $optionTextMap): string
|
|
{
|
|
if (!$answerRow) {
|
|
return '';
|
|
}
|
|
if (($question['type'] ?? '') === 'glass_scale_question') {
|
|
return qdb_format_glass_answer_json($answerRow['freeTextValue'] ?? null);
|
|
}
|
|
$aoid = $answerRow['answerOptionID'] ?? null;
|
|
if ($aoid && isset($optionTextMap[$aoid])) {
|
|
return (string)$optionTextMap[$aoid];
|
|
}
|
|
if (!empty($answerRow['freeTextValue'])) {
|
|
return (string)$answerRow['freeTextValue'];
|
|
}
|
|
if (isset($answerRow['numericValue']) && $answerRow['numericValue'] !== null && $answerRow['numericValue'] !== '') {
|
|
return (string)$answerRow['numericValue'];
|
|
}
|
|
return '';
|
|
}
|
|
|
|
function qdb_note_before_key(string $questionKey): string {
|
|
return $questionKey . '_note_before';
|
|
}
|
|
|
|
function qdb_note_after_key(string $questionKey): string {
|
|
return $questionKey . '_note_after';
|
|
}
|
|
|
|
/** Strip deprecated fields and persist questionKey + notes in config. */
|
|
function qdb_normalize_question_config(array $config, string $questionKey, ?string $noteBefore = null, ?string $noteAfter = null): array {
|
|
unset($config['multiline']);
|
|
$config['questionKey'] = $questionKey;
|
|
if ($noteBefore !== null) {
|
|
$nb = trim($noteBefore);
|
|
if ($nb !== '') {
|
|
$config['noteBefore'] = $nb;
|
|
} else {
|
|
unset($config['noteBefore']);
|
|
}
|
|
}
|
|
if ($noteAfter !== null) {
|
|
$na = trim($noteAfter);
|
|
if ($na !== '') {
|
|
$config['noteAfter'] = $na;
|
|
} else {
|
|
unset($config['noteAfter']);
|
|
}
|
|
}
|
|
return $config;
|
|
}
|
|
|
|
function qdb_parse_config_json($configJson): array {
|
|
if (is_array($configJson)) {
|
|
$config = $configJson;
|
|
} else {
|
|
$config = json_decode($configJson ?: '{}', true) ?: [];
|
|
}
|
|
unset($config['multiline']);
|
|
return $config;
|
|
}
|
|
|
|
/**
|
|
* @return list<array{key: string, labelDe: string}>
|
|
*/
|
|
function qdb_parse_glass_symptoms_body(array $body, array $config): array {
|
|
if (isset($body['glassSymptoms']) && is_array($body['glassSymptoms'])) {
|
|
$rows = [];
|
|
foreach ($body['glassSymptoms'] as $row) {
|
|
if (!is_array($row)) {
|
|
continue;
|
|
}
|
|
$key = trim((string)($row['key'] ?? ''));
|
|
if ($key === '') {
|
|
continue;
|
|
}
|
|
$key = qdb_validate_stable_key($key, 'Symptom key');
|
|
$rows[] = [
|
|
'key' => $key,
|
|
'labelDe' => trim((string)($row['labelDe'] ?? '')),
|
|
];
|
|
}
|
|
return $rows;
|
|
}
|
|
$out = [];
|
|
foreach ($config['symptoms'] ?? [] as $symptomKey) {
|
|
$key = trim((string)$symptomKey);
|
|
if ($key !== '') {
|
|
$out[] = ['key' => $key, 'labelDe' => ''];
|
|
}
|
|
}
|
|
return $out;
|
|
}
|
|
|
|
/** Apply ordered symptom keys from editor rows into question config. */
|
|
function qdb_apply_glass_symptoms_config(array $config, array $glassSymptoms): array {
|
|
$keys = [];
|
|
foreach ($glassSymptoms as $row) {
|
|
$keys[] = $row['key'];
|
|
}
|
|
if ($keys) {
|
|
$config['symptoms'] = $keys;
|
|
} else {
|
|
unset($config['symptoms']);
|
|
}
|
|
return $config;
|
|
}
|
|
|
|
/** Upsert German labels for glass-scale symptom string keys. */
|
|
function qdb_sync_glass_symptom_strings(PDO $pdo, array $glassSymptoms): void {
|
|
foreach ($glassSymptoms as $row) {
|
|
$key = $row['key'] ?? '';
|
|
$label = trim((string)($row['labelDe'] ?? ''));
|
|
if ($key === '' || $label === '') {
|
|
continue;
|
|
}
|
|
qdb_put_translation($pdo, 'string', $key, QDB_SOURCE_LANGUAGE, $label);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Symptom keys with German labels for editor / API consumers.
|
|
*
|
|
* @return list<array{key: string, labelDe: string}>
|
|
*/
|
|
function qdb_glass_symptoms_with_labels(PDO $pdo, array $config): array {
|
|
$rows = [];
|
|
foreach ($config['symptoms'] ?? [] as $symptomKey) {
|
|
$key = trim((string)$symptomKey);
|
|
if ($key === '') {
|
|
continue;
|
|
}
|
|
$label = qdb_string_german_label($pdo, $key);
|
|
if ($label === $key) {
|
|
$label = '';
|
|
}
|
|
$rows[] = ['key' => $key, 'labelDe' => $label];
|
|
}
|
|
return $rows;
|
|
}
|
|
|
|
/** Sync note German text into string_translation rows for the app. */
|
|
function qdb_sync_question_note_strings(PDO $pdo, string $questionKey, array $config): void {
|
|
if ($questionKey === '') {
|
|
return;
|
|
}
|
|
foreach ([
|
|
'noteBefore' => qdb_note_before_key($questionKey),
|
|
'noteAfter' => qdb_note_after_key($questionKey),
|
|
] as $field => $stringKey) {
|
|
$text = trim((string)($config[$field] ?? ''));
|
|
if ($text !== '') {
|
|
qdb_put_translation($pdo, 'string', $stringKey, QDB_SOURCE_LANGUAGE, $text);
|
|
}
|
|
}
|
|
}
|
|
|
|
function qdb_assert_unique_question_key(PDO $pdo, string $qnID, string $questionKey, ?string $excludeQuestionID = null): void {
|
|
$stmt = $pdo->prepare('SELECT questionID, configJson, defaultText FROM question WHERE questionnaireID = :qn');
|
|
$stmt->execute([':qn' => $qnID]);
|
|
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
|
if ($excludeQuestionID !== null && $row['questionID'] === $excludeQuestionID) {
|
|
continue;
|
|
}
|
|
$cfg = json_decode($row['configJson'] ?: '{}', true) ?: [];
|
|
$existing = qdb_question_key($cfg, $row['defaultText']);
|
|
if ($existing !== '' && $existing === $questionKey) {
|
|
json_error('CONFLICT', "Question key \"$questionKey\" already exists in this questionnaire", 409);
|
|
}
|
|
}
|
|
}
|
|
|
|
function qdb_assert_unique_option_key(PDO $pdo, string $questionID, string $optionKey, ?string $excludeOptionID = null): void {
|
|
$stmt = $pdo->prepare('SELECT answerOptionID, defaultText FROM answer_option WHERE questionID = :qid');
|
|
$stmt->execute([':qid' => $questionID]);
|
|
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
|
if ($excludeOptionID !== null && $row['answerOptionID'] === $excludeOptionID) {
|
|
continue;
|
|
}
|
|
if ($row['defaultText'] === $optionKey) {
|
|
json_error('CONFLICT', "Option key \"$optionKey\" already exists on this question", 409);
|
|
}
|
|
}
|
|
}
|
|
|
|
/** German label for an option (translation de, else legacy defaultText when not a stable key). */
|
|
function qdb_option_german_label(PDO $pdo, string $answerOptionID, string $storedDefaultText): string {
|
|
$tr = qdb_fetch_translation_map($pdo, 'answer_option', $answerOptionID);
|
|
$de = trim($tr[QDB_SOURCE_LANGUAGE] ?? '');
|
|
if ($de !== '') {
|
|
return $de;
|
|
}
|
|
if (!qdb_is_stable_key($storedDefaultText)) {
|
|
return $storedDefaultText;
|
|
}
|
|
return '';
|
|
}
|
|
|
|
/** German label for a string catalog key (symptoms, glass levels, etc.). */
|
|
function qdb_string_german_label(PDO $pdo, string $stringKey, array &$cache = []): string {
|
|
$stringKey = trim($stringKey);
|
|
if ($stringKey === '') {
|
|
return '';
|
|
}
|
|
if (isset($cache[$stringKey])) {
|
|
return $cache[$stringKey];
|
|
}
|
|
$tr = qdb_fetch_translation_map($pdo, 'string', $stringKey);
|
|
$de = trim($tr[QDB_SOURCE_LANGUAGE] ?? '');
|
|
$label = $de !== '' ? $de : $stringKey;
|
|
$cache[$stringKey] = $label;
|
|
return $label;
|
|
}
|
|
|
|
/** German question text for UI display. */
|
|
function qdb_question_german_label(PDO $pdo, array $questionRow): string {
|
|
$tr = qdb_fetch_translation_map($pdo, 'question', $questionRow['questionID']);
|
|
$de = trim($tr[QDB_SOURCE_LANGUAGE] ?? '');
|
|
if ($de !== '') {
|
|
return $de;
|
|
}
|
|
$defaultText = trim($questionRow['defaultText'] ?? '');
|
|
if ($defaultText !== '' && !qdb_is_stable_key($defaultText)) {
|
|
return $defaultText;
|
|
}
|
|
return qdb_question_local_id($questionRow['questionID']);
|
|
}
|
|
|
|
/**
|
|
* Result columns + option map using German labels (client detail, not CSV export).
|
|
*
|
|
* @return array{questions: array, resultColumns: array, optionTextMap: array, stringLabelCache: array}
|
|
*/
|
|
function qdb_display_questionnaire_context(PDO $pdo, string $questionnaireID): array {
|
|
$qStmt = $pdo->prepare(
|
|
'SELECT questionID, defaultText, type, orderIndex, configJson FROM question WHERE questionnaireID = :id ORDER BY orderIndex'
|
|
);
|
|
$qStmt->execute([':id' => $questionnaireID]);
|
|
$questions = $qStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
$stringLabelCache = [];
|
|
$resultColumns = [];
|
|
|
|
foreach ($questions as $q) {
|
|
$cfg = json_decode($q['configJson'] ?? '{}', true) ?: [];
|
|
$type = $q['type'] ?? '';
|
|
if (qdb_is_non_stored_answer_question_type($type)) {
|
|
continue;
|
|
}
|
|
if ($type === 'glass_scale_question') {
|
|
$symptoms = $cfg['symptoms'] ?? [];
|
|
if (is_array($symptoms) && $symptoms !== []) {
|
|
foreach ($symptoms as $symptomKey) {
|
|
$sk = trim((string)$symptomKey);
|
|
if ($sk === '') {
|
|
continue;
|
|
}
|
|
$resultColumns[] = [
|
|
'header' => qdb_string_german_label($pdo, $sk, $stringLabelCache),
|
|
'questionID' => $q['questionID'],
|
|
'symptomKey' => $sk,
|
|
'kind' => 'glass_symptom',
|
|
'question' => $q,
|
|
];
|
|
}
|
|
continue;
|
|
}
|
|
}
|
|
$resultColumns[] = [
|
|
'header' => qdb_question_german_label($pdo, $q),
|
|
'questionID' => $q['questionID'],
|
|
'symptomKey' => null,
|
|
'kind' => 'question',
|
|
'question' => $q,
|
|
];
|
|
}
|
|
|
|
$optionTextMap = [];
|
|
foreach ($questions as $q) {
|
|
$aoStmt = $pdo->prepare('SELECT answerOptionID, defaultText FROM answer_option WHERE questionID = :qid');
|
|
$aoStmt->execute([':qid' => $q['questionID']]);
|
|
foreach ($aoStmt->fetchAll(PDO::FETCH_ASSOC) as $ao) {
|
|
$optionTextMap[$ao['answerOptionID']] = qdb_option_german_label(
|
|
$pdo,
|
|
$ao['answerOptionID'],
|
|
$ao['defaultText']
|
|
);
|
|
}
|
|
}
|
|
|
|
return [
|
|
'questions' => $questions,
|
|
'resultColumns' => $resultColumns,
|
|
'optionTextMap' => $optionTextMap,
|
|
'stringLabelCache' => $stringLabelCache,
|
|
];
|
|
}
|
|
|
|
/** Format one answer cell for UI (German labels). */
|
|
function qdb_display_column_cell_value(
|
|
PDO $pdo,
|
|
array $column,
|
|
?array $answerRow,
|
|
array $optionTextMap,
|
|
array &$stringLabelCache
|
|
): string {
|
|
if (($column['kind'] ?? '') === 'glass_symptom') {
|
|
$raw = qdb_glass_symptom_answer_value(
|
|
$answerRow['freeTextValue'] ?? null,
|
|
(string)($column['symptomKey'] ?? '')
|
|
);
|
|
if ($raw === '') {
|
|
return '';
|
|
}
|
|
return qdb_string_german_label($pdo, $raw, $stringLabelCache);
|
|
}
|
|
if (($column['question']['type'] ?? '') === 'glass_scale_question') {
|
|
return qdb_format_glass_answer_json_display($pdo, $answerRow['freeTextValue'] ?? null, $stringLabelCache);
|
|
}
|
|
return qdb_format_client_answer_display($column['question'], $answerRow, $optionTextMap);
|
|
}
|
|
|
|
/** Glass-scale JSON with German symptom keys and level labels. */
|
|
function qdb_format_glass_answer_json_display(PDO $pdo, ?string $raw, array &$stringLabelCache): string
|
|
{
|
|
if ($raw === null || trim($raw) === '') {
|
|
return '';
|
|
}
|
|
$decoded = json_decode($raw, true);
|
|
if (!is_array($decoded)) {
|
|
return $raw;
|
|
}
|
|
$parts = [];
|
|
foreach ($decoded as $key => $val) {
|
|
$val = trim((string)$val);
|
|
if ($val === '') {
|
|
continue;
|
|
}
|
|
$symLabel = qdb_string_german_label($pdo, (string)$key, $stringLabelCache);
|
|
$levelLabel = qdb_string_german_label($pdo, $val, $stringLabelCache);
|
|
$parts[] = $symLabel . ': ' . $levelLabel;
|
|
}
|
|
return implode('; ', $parts);
|
|
}
|
|
|
|
/**
|
|
* Build translation entry lists for one questionnaire.
|
|
* Returns ['stringEntries' => [...], 'contentEntries' => [...]] (questions + options interleaved).
|
|
*/
|
|
function qdb_translation_entry_lists(PDO $pdo, string $qnID): array {
|
|
$qStmt = $pdo->prepare("
|
|
SELECT q.questionID, q.defaultText, q.type, q.configJson, q.orderIndex
|
|
FROM question q WHERE q.questionnaireID = :id ORDER BY q.orderIndex
|
|
");
|
|
$qStmt->execute([':id' => $qnID]);
|
|
$dbQuestions = $qStmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$stringKeys = [];
|
|
$stringEntries = [];
|
|
$contentEntries = [];
|
|
$addedSymptomKeys = [];
|
|
$appKeySet = qdb_app_string_key_set();
|
|
|
|
foreach ($dbQuestions as $dbQ) {
|
|
$qOrder = (int)($dbQ['orderIndex'] ?? 0);
|
|
$qLocal = qdb_question_local_id($dbQ['questionID']);
|
|
$config = json_decode($dbQ['configJson'] ?: '{}', true) ?: [];
|
|
$qKey = qdb_question_key($config, $dbQ['defaultText']);
|
|
$germanQ = trim($dbQ['defaultText']);
|
|
if ($qKey !== '' && qdb_is_stable_key($germanQ) && $germanQ === $qKey) {
|
|
$germanQ = '';
|
|
}
|
|
$contentEntries[] = [
|
|
'key' => $qKey !== '' ? $qKey : $dbQ['defaultText'],
|
|
'displayKey' => $qKey !== '' ? $qKey : (preg_match('/^[a-f0-9]{32}$/i', $qLocal) ? ('#' . ($qOrder + 1)) : $qLocal),
|
|
'germanText' => $germanQ,
|
|
'type' => 'question',
|
|
'entityId' => $dbQ['questionID'],
|
|
'sortOrder' => $qOrder * 1000,
|
|
];
|
|
|
|
$aoStmt = $pdo->prepare("
|
|
SELECT answerOptionID, defaultText, orderIndex
|
|
FROM answer_option WHERE questionID = :qid ORDER BY orderIndex
|
|
");
|
|
$aoStmt->execute([':qid' => $dbQ['questionID']]);
|
|
foreach ($aoStmt->fetchAll(PDO::FETCH_ASSOC) as $ao) {
|
|
$optKey = qdb_option_key($ao);
|
|
$optLabel = $optKey !== ''
|
|
? ($qLocal . ' · ' . $optKey)
|
|
: (preg_match('/^[a-f0-9]{32}$/i', $qLocal)
|
|
? ('#' . ($qOrder + 1) . ' · option ' . ((int)$ao['orderIndex'] + 1))
|
|
: ($qLocal . ' · option ' . ((int)$ao['orderIndex'] + 1)));
|
|
$contentEntries[] = [
|
|
'key' => $optKey !== '' ? $optKey : $ao['defaultText'],
|
|
'displayKey' => $optLabel,
|
|
'germanText' => qdb_option_german_label($pdo, $ao['answerOptionID'], $ao['defaultText']),
|
|
'type' => 'answer_option',
|
|
'entityId' => $ao['answerOptionID'],
|
|
'sortOrder' => $qOrder * 1000 + 10 + (int)($ao['orderIndex'] ?? 0),
|
|
];
|
|
}
|
|
|
|
foreach (['textKey', 'textKey1', 'textKey2', 'hint', 'hint1', 'hint2', 'otherOptionKey'] as $field) {
|
|
if (!empty($config[$field])) {
|
|
$stringKeys[$config[$field]] = true;
|
|
}
|
|
}
|
|
if (($dbQ['type'] ?? '') === 'glass_scale_question'
|
|
&& !empty($config['symptoms']) && is_array($config['symptoms'])) {
|
|
$symIdx = 0;
|
|
foreach ($config['symptoms'] as $s) {
|
|
$sk = trim((string)$s);
|
|
if ($sk === '' || isset($appKeySet[$sk]) || isset($addedSymptomKeys[$sk])) {
|
|
continue;
|
|
}
|
|
$addedSymptomKeys[$sk] = true;
|
|
$parentLabel = $qKey !== '' ? $qKey : $qLocal;
|
|
$stringEntries[] = [
|
|
'key' => $sk,
|
|
'displayKey' => $qLocal . ' · ' . $parentLabel . ' · ' . $sk,
|
|
'type' => 'string',
|
|
'entityId' => $sk,
|
|
'sortOrder' => $qOrder * 1000 + 5 + $symIdx,
|
|
];
|
|
$symIdx++;
|
|
}
|
|
}
|
|
if ($qKey !== '') {
|
|
if (!empty($config['noteBefore'])) {
|
|
$stringKeys[qdb_note_before_key($qKey)] = true;
|
|
}
|
|
if (!empty($config['noteAfter'])) {
|
|
$stringKeys[qdb_note_after_key($qKey)] = true;
|
|
}
|
|
}
|
|
}
|
|
|
|
$stringOrder = 0;
|
|
$skList = array_keys($stringKeys);
|
|
sort($skList, SORT_STRING);
|
|
foreach ($skList as $sk) {
|
|
if (isset($appKeySet[$sk])) {
|
|
continue;
|
|
}
|
|
$stringEntries[] = [
|
|
'key' => $sk,
|
|
'displayKey' => $sk,
|
|
'type' => 'string',
|
|
'entityId' => $sk,
|
|
'sortOrder' => $stringOrder++,
|
|
];
|
|
}
|
|
|
|
return ['stringEntries' => $stringEntries, 'contentEntries' => $contentEntries];
|
|
}
|
|
|
|
/** Attach translation texts and germanText to entry rows. */
|
|
function qdb_attach_translation_texts(array &$entries, array $translations): void {
|
|
foreach ($entries as &$e) {
|
|
$tr = $translations[$e['entityId']] ?? [];
|
|
$e['translations'] = (object)$tr;
|
|
$de = trim($tr[QDB_SOURCE_LANGUAGE] ?? '');
|
|
if ($de !== '') {
|
|
$e['germanText'] = $de;
|
|
} elseif (!empty($e['germanDefault'])) {
|
|
$e['germanText'] = $e['germanDefault'];
|
|
} else {
|
|
$e['germanText'] = $e['key'];
|
|
}
|
|
unset($e['germanDefault']);
|
|
}
|
|
unset($e);
|
|
}
|
|
|
|
/** Load all translation rows for a flat entry list. */
|
|
function qdb_load_translations_for_entries(PDO $pdo, array $entries): array {
|
|
$translations = [];
|
|
|
|
$qIds = array_values(array_filter(array_map(
|
|
fn($e) => $e['type'] === 'question' ? $e['entityId'] : null,
|
|
$entries
|
|
)));
|
|
if ($qIds) {
|
|
$ph = implode(',', array_fill(0, count($qIds), '?'));
|
|
$stmt = $pdo->prepare("SELECT questionID AS entityId, languageCode, text FROM question_translation WHERE questionID IN ($ph)");
|
|
$stmt->execute($qIds);
|
|
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
|
|
$translations[$r['entityId']][$r['languageCode']] = $r['text'];
|
|
}
|
|
}
|
|
|
|
$aoIds = array_values(array_filter(array_map(
|
|
fn($e) => $e['type'] === 'answer_option' ? $e['entityId'] : null,
|
|
$entries
|
|
)));
|
|
if ($aoIds) {
|
|
$ph = implode(',', array_fill(0, count($aoIds), '?'));
|
|
$stmt = $pdo->prepare("SELECT answerOptionID AS entityId, languageCode, text FROM answer_option_translation WHERE answerOptionID IN ($ph)");
|
|
$stmt->execute($aoIds);
|
|
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
|
|
$translations[$r['entityId']][$r['languageCode']] = $r['text'];
|
|
}
|
|
}
|
|
|
|
$sKeys = array_values(array_filter(array_map(
|
|
fn($e) => ($e['type'] === 'string' || $e['type'] === 'app_string') ? $e['entityId'] : null,
|
|
$entries
|
|
)));
|
|
if ($sKeys) {
|
|
$ph = implode(',', array_fill(0, count($sKeys), '?'));
|
|
$stmt = $pdo->prepare("SELECT stringKey AS entityId, languageCode, text FROM string_translation WHERE stringKey IN ($ph)");
|
|
$stmt->execute($sKeys);
|
|
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
|
|
$translations[$r['entityId']][$r['languageCode']] = $r['text'];
|
|
}
|
|
}
|
|
|
|
return $translations;
|
|
}
|
|
|
|
/**
|
|
* Flat language -> stringKey -> text map for global app UI strings only.
|
|
* @return array<string, array<string, string>>
|
|
*/
|
|
function qdb_build_app_translations_map(PDO $pdo): array {
|
|
$entries = qdb_app_ui_string_entries_merged($pdo);
|
|
$translations = qdb_load_translations_for_entries($pdo, $entries);
|
|
$defaults = qdb_app_string_catalog()['germanDefaults'] ?? [];
|
|
$result = [];
|
|
foreach ($entries as $e) {
|
|
$key = $e['key'];
|
|
foreach ($translations[$e['entityId']] ?? [] as $lang => $text) {
|
|
$result[$lang][$key] = $text;
|
|
}
|
|
if (!isset($result[QDB_SOURCE_LANGUAGE][$key]) && !empty($defaults[$key])) {
|
|
$result[QDB_SOURCE_LANGUAGE][$key] = $defaults[$key];
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Flat language -> stringKey -> text map for one questionnaire (questions, options, config strings).
|
|
* Excludes keys from the global app UI catalog.
|
|
* @return array<string, array<string, string>>
|
|
*/
|
|
function qdb_build_questionnaire_translations_map(PDO $pdo, string $qnID): array {
|
|
$lists = qdb_translation_entry_lists($pdo, $qnID);
|
|
$entries = array_merge($lists['stringEntries'], $lists['contentEntries']);
|
|
$translations = $entries ? qdb_load_translations_for_entries($pdo, $entries) : [];
|
|
$result = [];
|
|
$stringLabelCache = [];
|
|
$appDefaults = qdb_app_string_catalog()['germanDefaults'] ?? [];
|
|
|
|
foreach ($entries as $e) {
|
|
$key = $e['key'];
|
|
if ($key === '') {
|
|
continue;
|
|
}
|
|
foreach ($translations[$e['entityId']] ?? [] as $lang => $text) {
|
|
$text = trim((string)$text);
|
|
if ($text !== '') {
|
|
$result[$lang][$key] = $text;
|
|
}
|
|
}
|
|
$german = trim($e['germanText'] ?? '');
|
|
if ($german === '' && ($e['type'] ?? '') === 'string') {
|
|
$trDe = trim($translations[$e['entityId']][QDB_SOURCE_LANGUAGE] ?? '');
|
|
if ($trDe !== '' && !qdb_is_stable_key($trDe)) {
|
|
$german = $trDe;
|
|
} elseif (!empty($appDefaults[$key])) {
|
|
$german = trim((string)$appDefaults[$key]);
|
|
} else {
|
|
$fetched = trim(qdb_string_german_label($pdo, $key, $stringLabelCache));
|
|
if ($fetched !== '' && !qdb_is_stable_key($fetched)) {
|
|
$german = $fetched;
|
|
}
|
|
}
|
|
}
|
|
if ($german !== '' && !isset($result[QDB_SOURCE_LANGUAGE][$key])) {
|
|
$result[QDB_SOURCE_LANGUAGE][$key] = $german;
|
|
}
|
|
}
|
|
|
|
$qStmt = $pdo->prepare(
|
|
'SELECT questionID, defaultText, configJson FROM question WHERE questionnaireID = :id ORDER BY orderIndex'
|
|
);
|
|
$qStmt->execute([':id' => $qnID]);
|
|
foreach ($qStmt->fetchAll(PDO::FETCH_ASSOC) as $dbQ) {
|
|
$config = json_decode($dbQ['configJson'] ?: '{}', true) ?: [];
|
|
$qKey = qdb_question_key($config, $dbQ['defaultText']);
|
|
if ($qKey === '') {
|
|
continue;
|
|
}
|
|
$qGerman = qdb_question_german_label($pdo, $dbQ);
|
|
if ($qGerman !== '' && !qdb_is_stable_key($qGerman)) {
|
|
$result[QDB_SOURCE_LANGUAGE][$qKey] = $qGerman;
|
|
}
|
|
$noteBefore = trim((string)($config['noteBefore'] ?? ''));
|
|
if ($noteBefore !== '') {
|
|
$result[QDB_SOURCE_LANGUAGE][qdb_note_before_key($qKey)] = $noteBefore;
|
|
$textKey = trim((string)($config['textKey'] ?? ''));
|
|
if ($textKey !== '') {
|
|
$result[QDB_SOURCE_LANGUAGE][$textKey] = $noteBefore;
|
|
}
|
|
}
|
|
$noteAfter = trim((string)($config['noteAfter'] ?? ''));
|
|
if ($noteAfter !== '') {
|
|
$result[QDB_SOURCE_LANGUAGE][qdb_note_after_key($qKey)] = $noteAfter;
|
|
}
|
|
}
|
|
|
|
$aoStmt = $pdo->prepare(
|
|
'SELECT answerOptionID, defaultText FROM answer_option WHERE questionID IN (
|
|
SELECT questionID FROM question WHERE questionnaireID = :id
|
|
) ORDER BY questionID, orderIndex'
|
|
);
|
|
$aoStmt->execute([':id' => $qnID]);
|
|
foreach ($aoStmt->fetchAll(PDO::FETCH_ASSOC) as $ao) {
|
|
$optKey = qdb_option_key($ao);
|
|
if ($optKey === '') {
|
|
continue;
|
|
}
|
|
$optGerman = qdb_option_german_label($pdo, $ao['answerOptionID'], $ao['defaultText']);
|
|
if ($optGerman !== '' && !qdb_is_stable_key($optGerman)) {
|
|
$result[QDB_SOURCE_LANGUAGE][$optKey] = $optGerman;
|
|
}
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/** @return array<string, string> */
|
|
function qdb_translation_rows_to_map(array $rows): array {
|
|
$map = [];
|
|
foreach ($rows as $row) {
|
|
if (is_array($row) && isset($row['languageCode'])) {
|
|
$map[$row['languageCode']] = $row['text'] ?? '';
|
|
}
|
|
}
|
|
return $map;
|
|
}
|
|
|
|
/** @return array<string, string> */
|
|
function qdb_fetch_translation_map(PDO $pdo, string $type, string $entityId): array {
|
|
if ($type === 'question') {
|
|
$stmt = $pdo->prepare('SELECT languageCode, text FROM question_translation WHERE questionID = :id');
|
|
} elseif ($type === 'answer_option') {
|
|
$stmt = $pdo->prepare('SELECT languageCode, text FROM answer_option_translation WHERE answerOptionID = :id');
|
|
} else {
|
|
$stmt = $pdo->prepare('SELECT languageCode, text FROM string_translation WHERE stringKey = :id');
|
|
}
|
|
$stmt->execute([':id' => $entityId]);
|
|
return qdb_translation_rows_to_map($stmt->fetchAll(PDO::FETCH_ASSOC));
|
|
}
|
|
|
|
function qdb_apply_translation_map(PDO $pdo, string $type, string $entityId, array $map): void {
|
|
foreach ($map as $lang => $text) {
|
|
if (!is_string($lang) || $lang === '') {
|
|
continue;
|
|
}
|
|
qdb_put_translation($pdo, $type, $entityId, $lang, (string)$text);
|
|
}
|
|
}
|
|
|
|
function qdb_question_local_id(string $questionID, ?string $questionnaireID = null): string {
|
|
$pos = strrpos($questionID, '__');
|
|
return $pos !== false ? substr($questionID, $pos + 2) : $questionID;
|
|
}
|
|
|
|
/** Column / export header: stable question key, else local id (q1, q2, …). */
|
|
function qdb_question_column_label(array $config, string $defaultText, string $questionID, ?string $questionnaireID = null): string {
|
|
$key = qdb_question_key($config, $defaultText);
|
|
return $key !== '' ? $key : qdb_question_local_id($questionID, $questionnaireID);
|
|
}
|
|
|
|
function qdb_make_question_id(string $questionnaireID, string $localId): string {
|
|
return $questionnaireID . '__' . $localId;
|
|
}
|
|
|
|
/** Next short id for a new question (q1, q2, …) within one questionnaire. */
|
|
function qdb_allocate_question_local_id(PDO $pdo, string $questionnaireID): string {
|
|
$stmt = $pdo->prepare('SELECT questionID FROM question WHERE questionnaireID = :id');
|
|
$stmt->execute([':id' => $questionnaireID]);
|
|
$max = 0;
|
|
foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $questionID) {
|
|
$local = qdb_question_local_id($questionID);
|
|
if (preg_match('/^q(\d+)$/i', $local, $m)) {
|
|
$max = max($max, (int)$m[1]);
|
|
}
|
|
}
|
|
return 'q' . ($max + 1);
|
|
}
|
|
|
|
/** Human-readable Key column label (not the app translation lookup key). */
|
|
function qdb_translation_row_label(array $e): string {
|
|
if (!empty($e['displayKey']) && !preg_match('/^[a-f0-9]{32}$/i', $e['displayKey'])) {
|
|
return $e['displayKey'];
|
|
}
|
|
$type = $e['type'] ?? '';
|
|
$key = $e['key'] ?? '';
|
|
if ($type === 'question' || $type === 'answer_option') {
|
|
if ($key !== '') {
|
|
$short = mb_strlen($key) > 56 ? mb_substr($key, 0, 56) . '…' : $key;
|
|
return $short;
|
|
}
|
|
}
|
|
$id = $e['entityId'] ?? '';
|
|
$pos = strrpos($id, '__');
|
|
if ($pos !== false) {
|
|
return substr($id, $pos + 2);
|
|
}
|
|
return $key !== '' ? $key : $id;
|
|
}
|
|
|
|
/** Export one questionnaire with structure + all translations (portable JSON). */
|
|
function qdb_export_questionnaire_bundle(PDO $pdo, string $qnID): ?array {
|
|
require_once __DIR__ . '/lib/scoring.php';
|
|
require_once __DIR__ . '/lib/questionnaire_structure.php';
|
|
$qn = $pdo->prepare('SELECT * FROM questionnaire WHERE questionnaireID = :id');
|
|
$qn->execute([':id' => $qnID]);
|
|
$qnRow = $qn->fetch(PDO::FETCH_ASSOC);
|
|
if (!$qnRow) {
|
|
return null;
|
|
}
|
|
|
|
$qStmt = $pdo->prepare(
|
|
'SELECT questionID, defaultText, type, orderIndex, isRequired, configJson
|
|
FROM question WHERE questionnaireID = :id AND ' . qdb_active_questions_clause('question') . '
|
|
ORDER BY orderIndex'
|
|
);
|
|
$qStmt->execute([':id' => $qnID]);
|
|
$questionsOut = [];
|
|
|
|
foreach ($qStmt->fetchAll(PDO::FETCH_ASSOC) as $dbQ) {
|
|
$localId = qdb_question_local_id($dbQ['questionID'], $qnID);
|
|
$config = json_decode($dbQ['configJson'] ?: '{}', true) ?: [];
|
|
|
|
$optionsOut = [];
|
|
$aoStmt = $pdo->prepare(
|
|
'SELECT answerOptionID, defaultText, points, orderIndex, nextQuestionId
|
|
FROM answer_option WHERE questionID = :qid AND ' . qdb_active_options_clause('answer_option') . '
|
|
ORDER BY orderIndex'
|
|
);
|
|
$aoStmt->execute([':qid' => $dbQ['questionID']]);
|
|
$qKey = qdb_question_key($config, $dbQ['defaultText']);
|
|
foreach ($aoStmt->fetchAll(PDO::FETCH_ASSOC) as $ao) {
|
|
$optKey = qdb_option_key($ao);
|
|
$optTr = qdb_fetch_translation_map($pdo, 'answer_option', $ao['answerOptionID']);
|
|
$optGerman = trim($optTr[QDB_SOURCE_LANGUAGE] ?? '');
|
|
if ($optGerman === '' && !qdb_is_stable_key($ao['defaultText'])) {
|
|
$optGerman = $ao['defaultText'];
|
|
}
|
|
$optionsOut[] = [
|
|
'optionKey' => $optKey !== '' ? $optKey : $ao['defaultText'],
|
|
'defaultText' => $optGerman,
|
|
'points' => (int)$ao['points'],
|
|
'orderIndex' => (int)$ao['orderIndex'],
|
|
'nextQuestionId' => $ao['nextQuestionId'] ?? '',
|
|
'translations' => $optTr,
|
|
];
|
|
}
|
|
|
|
$questionsOut[] = [
|
|
'localId' => $localId,
|
|
'questionKey' => $qKey,
|
|
'defaultText' => $dbQ['defaultText'],
|
|
'type' => $dbQ['type'],
|
|
'orderIndex' => (int)$dbQ['orderIndex'],
|
|
'isRequired' => (int)$dbQ['isRequired'],
|
|
'config' => $config,
|
|
'answerOptions' => $optionsOut,
|
|
'scoreRules' => qdb_get_score_rules_for_question($pdo, $dbQ['questionID']),
|
|
'translations' => qdb_fetch_translation_map($pdo, 'question', $dbQ['questionID']),
|
|
];
|
|
}
|
|
|
|
$lists = qdb_translation_entry_lists($pdo, $qnID);
|
|
$stringTranslations = [];
|
|
if (!empty($lists['stringEntries'])) {
|
|
$tr = qdb_load_translations_for_entries($pdo, $lists['stringEntries']);
|
|
foreach ($lists['stringEntries'] as $e) {
|
|
$stringTranslations[] = [
|
|
'stringKey' => $e['key'],
|
|
'translations' => $tr[$e['entityId']] ?? [],
|
|
];
|
|
}
|
|
}
|
|
|
|
return [
|
|
'questionnaire' => [
|
|
'questionnaireID' => $qnRow['questionnaireID'],
|
|
'name' => $qnRow['name'],
|
|
'version' => $qnRow['version'],
|
|
'state' => $qnRow['state'],
|
|
'orderIndex' => (int)$qnRow['orderIndex'],
|
|
'showPoints' => (int)$qnRow['showPoints'],
|
|
'conditionJson' => $qnRow['conditionJson'] ?: '{}',
|
|
'structureRevision' => qdb_questionnaire_structure_revision($pdo, $qnID),
|
|
],
|
|
'questions' => $questionsOut,
|
|
'stringTranslations' => $stringTranslations,
|
|
'translationsFlat' => qdb_build_questionnaire_translations_map($pdo, $qnID),
|
|
];
|
|
}
|
|
|
|
/** Export every questionnaire as a portable bundle. */
|
|
function qdb_export_all_questionnaires_bundle(PDO $pdo): array {
|
|
$ids = $pdo->query('SELECT questionnaireID FROM questionnaire ORDER BY orderIndex, name')
|
|
->fetchAll(PDO::FETCH_COLUMN);
|
|
$items = [];
|
|
foreach ($ids as $id) {
|
|
$item = qdb_export_questionnaire_bundle($pdo, $id);
|
|
if ($item !== null) {
|
|
$items[] = $item;
|
|
}
|
|
}
|
|
return [
|
|
'exportVersion' => 2,
|
|
'exportedAt' => time(),
|
|
'sourceLanguage' => QDB_SOURCE_LANGUAGE,
|
|
'questionnaireCount' => count($items),
|
|
'questionnaires' => $items,
|
|
'scoringProfiles' => qdb_export_scoring_profiles_bundle($pdo),
|
|
];
|
|
}
|
|
|
|
/** Export all scoring profiles for bundle portability. */
|
|
function qdb_export_scoring_profiles_bundle(PDO $pdo): array {
|
|
require_once __DIR__ . '/lib/scoring.php';
|
|
return qdb_list_scoring_profiles($pdo);
|
|
}
|
|
|
|
/** Remove questionnaire and related rows (full replace import; not per-question retire). */
|
|
function qdb_delete_questionnaire_cascade(PDO $pdo, string $qnID): void {
|
|
$qIds = $pdo->prepare('SELECT questionID FROM question WHERE questionnaireID = :id');
|
|
$qIds->execute([':id' => $qnID]);
|
|
$questionIDs = $qIds->fetchAll(PDO::FETCH_COLUMN);
|
|
|
|
foreach ($questionIDs as $qid) {
|
|
$aoIds = $pdo->prepare('SELECT answerOptionID FROM answer_option WHERE questionID = :qid');
|
|
$aoIds->execute([':qid' => $qid]);
|
|
foreach ($aoIds->fetchAll(PDO::FETCH_COLUMN) as $aoid) {
|
|
$pdo->prepare('DELETE FROM answer_option_translation WHERE answerOptionID = :id')
|
|
->execute([':id' => $aoid]);
|
|
}
|
|
$pdo->prepare('DELETE FROM answer_option WHERE questionID = :qid')->execute([':qid' => $qid]);
|
|
$pdo->prepare('DELETE FROM question_translation WHERE questionID = :qid')->execute([':qid' => $qid]);
|
|
if (qdb_table_exists($pdo, 'client_answer_submission')) {
|
|
$pdo->prepare('DELETE FROM client_answer_submission WHERE questionID = :qid')
|
|
->execute([':qid' => $qid]);
|
|
}
|
|
$pdo->prepare('DELETE FROM client_answer WHERE questionID = :qid')->execute([':qid' => $qid]);
|
|
}
|
|
if (qdb_table_exists($pdo, 'questionnaire_structure_snapshot')) {
|
|
$pdo->prepare('DELETE FROM questionnaire_structure_snapshot WHERE questionnaireID = :id')
|
|
->execute([':id' => $qnID]);
|
|
}
|
|
if (qdb_table_exists($pdo, 'questionnaire_submission')) {
|
|
$pdo->prepare('DELETE FROM questionnaire_submission WHERE questionnaireID = :id')
|
|
->execute([':id' => $qnID]);
|
|
}
|
|
$pdo->prepare('DELETE FROM question WHERE questionnaireID = :id')->execute([':id' => $qnID]);
|
|
$pdo->prepare('DELETE FROM completed_questionnaire WHERE questionnaireID = :id')->execute([':id' => $qnID]);
|
|
$pdo->prepare('DELETE FROM questionnaire WHERE questionnaireID = :id')->execute([':id' => $qnID]);
|
|
}
|
|
|
|
/**
|
|
* Import one questionnaire from a bundle item.
|
|
* @return array{questionnaireID: string, created: bool, replaced: bool}
|
|
*/
|
|
function qdb_import_questionnaire_bundle(PDO $pdo, array $item, bool $replaceIfExists = false): array {
|
|
$meta = $item['questionnaire'] ?? [];
|
|
if (empty($meta['name'])) {
|
|
json_error('MISSING_FIELDS', 'questionnaire.name is required', 400);
|
|
}
|
|
|
|
$wantedId = trim($meta['questionnaireID'] ?? '');
|
|
$chk = $wantedId !== '' ? $pdo->prepare('SELECT 1 FROM questionnaire WHERE questionnaireID = :id') : null;
|
|
if ($chk) {
|
|
$chk->execute([':id' => $wantedId]);
|
|
}
|
|
$exists = $chk && (bool)$chk->fetch();
|
|
|
|
$replaced = false;
|
|
if ($exists && $replaceIfExists) {
|
|
qdb_delete_questionnaire_cascade($pdo, $wantedId);
|
|
$replaced = true;
|
|
$qnID = $wantedId;
|
|
} elseif ($exists) {
|
|
$qnID = bin2hex(random_bytes(16));
|
|
} elseif ($wantedId !== '') {
|
|
$qnID = $wantedId;
|
|
} else {
|
|
$qnID = bin2hex(random_bytes(16));
|
|
}
|
|
|
|
$order = (int)($meta['orderIndex'] ?? 0);
|
|
if ($order === 0) {
|
|
$order = (int)$pdo->query('SELECT COALESCE(MAX(orderIndex),0)+1 FROM questionnaire')->fetchColumn();
|
|
}
|
|
|
|
$condJson = $meta['conditionJson'] ?? '{}';
|
|
if (is_array($condJson)) {
|
|
$condJson = json_encode($condJson, JSON_UNESCAPED_UNICODE);
|
|
}
|
|
|
|
$catKey = trim($meta['categoryKey'] ?? '');
|
|
$pdo->prepare("INSERT INTO questionnaire (questionnaireID, name, version, state, orderIndex, showPoints, conditionJson, categoryKey)
|
|
VALUES (:id, :n, :v, :s, :o, :sp, :cj, :ck)")
|
|
->execute([
|
|
':id' => $qnID,
|
|
':n' => trim($meta['name']),
|
|
':v' => trim($meta['version'] ?? ''),
|
|
':s' => trim($meta['state'] ?? 'draft'),
|
|
':o' => $order,
|
|
':sp' => (int)($meta['showPoints'] ?? 0),
|
|
':cj' => $condJson ?: '{}',
|
|
':ck' => $catKey,
|
|
]);
|
|
|
|
foreach ($item['questions'] ?? [] as $q) {
|
|
$localId = trim($q['localId'] ?? '');
|
|
if ($localId === '') {
|
|
$localId = 'q_' . bin2hex(random_bytes(4));
|
|
}
|
|
$questionID = $qnID . '__' . $localId;
|
|
|
|
$config = qdb_parse_config_json($q['config'] ?? $q['configJson'] ?? []);
|
|
$questionKey = trim((string)($q['questionKey'] ?? $config['questionKey'] ?? ''));
|
|
$text = trim($q['defaultText'] ?? '');
|
|
$qTr = $q['translations'] ?? [];
|
|
if (is_array($qTr) && isset($qTr[0]['languageCode'])) {
|
|
$qTr = qdb_translation_rows_to_map($qTr);
|
|
}
|
|
if ($questionKey === '' && qdb_is_stable_key($text) && trim($qTr[QDB_SOURCE_LANGUAGE] ?? '') !== '' && $qTr[QDB_SOURCE_LANGUAGE] !== $text) {
|
|
$questionKey = $text;
|
|
$text = trim($qTr[QDB_SOURCE_LANGUAGE]);
|
|
}
|
|
if ($questionKey === '') {
|
|
json_error(
|
|
'MISSING_FIELDS',
|
|
"Question key is required for {$qnID} / {$localId} (stable key [a-z][a-z0-9_]*)",
|
|
400
|
|
);
|
|
}
|
|
$questionKey = qdb_validate_stable_key($questionKey, 'Question key');
|
|
// Duplicate question keys in one questionnaire are allowed (same prompt in different branches).
|
|
|
|
qdb_require_non_empty_german($text, 'Question German text');
|
|
$noteBefore = $config['noteBefore'] ?? null;
|
|
$noteAfter = $config['noteAfter'] ?? null;
|
|
$config = qdb_normalize_question_config($config, $questionKey, $noteBefore, $noteAfter);
|
|
$configJson = json_encode($config, JSON_UNESCAPED_UNICODE);
|
|
|
|
$pdo->prepare("INSERT INTO question (questionID, questionnaireID, defaultText, type, orderIndex, isRequired, configJson)
|
|
VALUES (:id, :qid, :t, :ty, :o, :r, :cj)")
|
|
->execute([
|
|
':id' => $questionID,
|
|
':qid' => $qnID,
|
|
':t' => $text,
|
|
':ty' => trim($q['type'] ?? ''),
|
|
':o' => (int)($q['orderIndex'] ?? 0),
|
|
':r' => (int)($q['isRequired'] ?? 0),
|
|
':cj' => $configJson,
|
|
]);
|
|
|
|
qdb_upsert_source_translation($pdo, 'question', $questionID, $text);
|
|
qdb_apply_translation_map($pdo, 'question', $questionID, is_array($qTr) ? $qTr : []);
|
|
qdb_sync_question_note_strings($pdo, $questionKey, $config);
|
|
|
|
if (!empty($q['scoreRules']) && is_array($q['scoreRules'])) {
|
|
require_once __DIR__ . '/lib/scoring.php';
|
|
qdb_sync_question_score_rules($pdo, $questionID, $q['scoreRules']);
|
|
}
|
|
|
|
foreach ($q['answerOptions'] ?? [] as $opt) {
|
|
$optKey = trim((string)($opt['optionKey'] ?? ''));
|
|
$optGerman = trim($opt['defaultText'] ?? '');
|
|
$oTr = $opt['translations'] ?? [];
|
|
if (is_array($oTr) && isset($oTr[0]['languageCode'])) {
|
|
$oTr = qdb_translation_rows_to_map($oTr);
|
|
}
|
|
if ($optKey === '' && qdb_is_stable_key($optGerman) && trim($oTr[QDB_SOURCE_LANGUAGE] ?? '') !== '' && $oTr[QDB_SOURCE_LANGUAGE] !== $optGerman) {
|
|
$optKey = $optGerman;
|
|
$optGerman = trim($oTr[QDB_SOURCE_LANGUAGE]);
|
|
}
|
|
if ($optKey === '' && qdb_is_stable_key($optGerman)) {
|
|
$optKey = $optGerman;
|
|
$optGerman = trim($oTr[QDB_SOURCE_LANGUAGE] ?? $optGerman);
|
|
}
|
|
if ($optKey === '') {
|
|
json_error(
|
|
'MISSING_FIELDS',
|
|
"Option key is required for {$qnID} / {$localId} option #" . ((int)($opt['orderIndex'] ?? 0) + 1),
|
|
400
|
|
);
|
|
}
|
|
$optKey = qdb_validate_stable_key($optKey, 'Option key');
|
|
if ($optGerman === '') {
|
|
$optGerman = trim($oTr[QDB_SOURCE_LANGUAGE] ?? '');
|
|
}
|
|
qdb_require_non_empty_german($optGerman, 'Answer option German text');
|
|
|
|
$aoId = bin2hex(random_bytes(16));
|
|
$pdo->prepare("INSERT INTO answer_option (answerOptionID, questionID, defaultText, points, orderIndex, nextQuestionId)
|
|
VALUES (:id, :qid, :t, :p, :o, :nq)")
|
|
->execute([
|
|
':id' => $aoId,
|
|
':qid' => $questionID,
|
|
':t' => $optKey,
|
|
':p' => (int)($opt['points'] ?? 0),
|
|
':o' => (int)($opt['orderIndex'] ?? 0),
|
|
':nq' => trim($opt['nextQuestionId'] ?? ''),
|
|
]);
|
|
qdb_upsert_source_translation($pdo, 'answer_option', $aoId, $optGerman);
|
|
qdb_apply_translation_map($pdo, 'answer_option', $aoId, is_array($oTr) ? $oTr : []);
|
|
}
|
|
}
|
|
|
|
foreach ($item['stringTranslations'] ?? [] as $st) {
|
|
$sk = trim($st['stringKey'] ?? '');
|
|
if ($sk === '') {
|
|
continue;
|
|
}
|
|
$map = $st['translations'] ?? [];
|
|
if (is_array($map) && isset($map[0]['languageCode'])) {
|
|
$map = qdb_translation_rows_to_map($map);
|
|
}
|
|
qdb_apply_translation_map($pdo, 'string', $sk, is_array($map) ? $map : []);
|
|
}
|
|
|
|
return [
|
|
'questionnaireID' => $qnID,
|
|
'created' => true,
|
|
'replaced' => $replaced,
|
|
];
|
|
}
|
|
|
|
/** One translation row for a portable translations JSON bundle. */
|
|
function qdb_translation_entry_to_bundle_row(array $e, ?string $questionnaireID = null, ?string $questionnaireName = null): array {
|
|
$tr = $e['translations'] ?? [];
|
|
if ($tr instanceof stdClass) {
|
|
$tr = (array)$tr;
|
|
}
|
|
if (is_array($tr) && isset($tr[0]) && is_array($tr[0]) && isset($tr[0]['languageCode'])) {
|
|
$tr = qdb_translation_rows_to_map($tr);
|
|
}
|
|
$row = [
|
|
'type' => $e['type'],
|
|
'entityId' => $e['entityId'],
|
|
'key' => $e['key'] ?? '',
|
|
'translations' => is_array($tr) ? $tr : [],
|
|
];
|
|
if (!empty($e['displayKey'])) {
|
|
$row['displayKey'] = $e['displayKey'];
|
|
}
|
|
if ($questionnaireID !== null && $questionnaireID !== '') {
|
|
$row['questionnaireID'] = $questionnaireID;
|
|
}
|
|
if ($questionnaireName !== null && $questionnaireName !== '') {
|
|
$row['questionnaireName'] = $questionnaireName;
|
|
}
|
|
return $row;
|
|
}
|
|
|
|
/** @return array<string, string> */
|
|
function qdb_normalize_bundle_translations($raw): array {
|
|
if (!is_array($raw)) {
|
|
return [];
|
|
}
|
|
if (isset($raw[0]) && is_array($raw[0]) && isset($raw[0]['languageCode'])) {
|
|
return qdb_translation_rows_to_map($raw);
|
|
}
|
|
$map = [];
|
|
foreach ($raw as $lang => $text) {
|
|
if (is_string($lang) && $lang !== '') {
|
|
$map[$lang] = (string)$text;
|
|
}
|
|
}
|
|
return $map;
|
|
}
|
|
|
|
function qdb_translation_entity_exists(PDO $pdo, string $type, string $entityId): bool {
|
|
if ($type === 'string' || $type === 'app_string') {
|
|
return true;
|
|
}
|
|
if ($type === 'question') {
|
|
$stmt = $pdo->prepare('SELECT 1 FROM question WHERE questionID = :id LIMIT 1');
|
|
} elseif ($type === 'answer_option') {
|
|
$stmt = $pdo->prepare('SELECT 1 FROM answer_option WHERE answerOptionID = :id LIMIT 1');
|
|
} else {
|
|
return false;
|
|
}
|
|
$stmt->execute([':id' => $entityId]);
|
|
return (bool)$stmt->fetchColumn();
|
|
}
|
|
|
|
/** Export all translations (languages + flat entries) as portable JSON. */
|
|
function qdb_export_translations_bundle(PDO $pdo): array {
|
|
$languages = $pdo->query('SELECT languageCode, name FROM language ORDER BY languageCode')
|
|
->fetchAll(PDO::FETCH_ASSOC);
|
|
if (!array_filter($languages, fn($l) => $l['languageCode'] === QDB_SOURCE_LANGUAGE)) {
|
|
array_unshift($languages, ['languageCode' => QDB_SOURCE_LANGUAGE, 'name' => 'German']);
|
|
}
|
|
qdb_ensure_source_language($pdo);
|
|
|
|
$exportEntries = [];
|
|
$appEntries = qdb_app_ui_string_entries_for_website($pdo);
|
|
$appTranslations = qdb_load_translations_for_entries($pdo, $appEntries);
|
|
qdb_attach_translation_texts($appEntries, $appTranslations);
|
|
foreach ($appEntries as $e) {
|
|
$exportEntries[] = qdb_translation_entry_to_bundle_row($e);
|
|
}
|
|
|
|
$qnRows = $pdo->query('SELECT questionnaireID, name FROM questionnaire ORDER BY orderIndex, name')
|
|
->fetchAll(PDO::FETCH_ASSOC);
|
|
foreach ($qnRows as $qn) {
|
|
$lists = qdb_translation_entry_lists($pdo, $qn['questionnaireID']);
|
|
$entries = array_merge($lists['stringEntries'], $lists['contentEntries']);
|
|
if (!$entries) {
|
|
continue;
|
|
}
|
|
$translations = qdb_load_translations_for_entries($pdo, $entries);
|
|
qdb_attach_translation_texts($entries, $translations);
|
|
foreach ($entries as $e) {
|
|
$exportEntries[] = qdb_translation_entry_to_bundle_row(
|
|
$e,
|
|
$qn['questionnaireID'],
|
|
$qn['name']
|
|
);
|
|
}
|
|
}
|
|
|
|
return [
|
|
'exportVersion' => 1,
|
|
'exportedAt' => time(),
|
|
'sourceLanguage' => QDB_SOURCE_LANGUAGE,
|
|
'languageCount' => count($languages),
|
|
'languages' => $languages,
|
|
'entryCount' => count($exportEntries),
|
|
'entries' => $exportEntries,
|
|
];
|
|
}
|
|
|
|
/** Import translations bundle (upsert languages and translation rows). */
|
|
function qdb_import_translations_bundle(PDO $pdo, array $bundle): array {
|
|
$entries = $bundle['entries'] ?? null;
|
|
if (!is_array($entries)) {
|
|
json_error('MISSING_FIELDS', 'bundle.entries array is required', 400);
|
|
}
|
|
|
|
qdb_ensure_source_language($pdo);
|
|
foreach ($bundle['languages'] ?? [] as $langRow) {
|
|
if (!is_array($langRow)) {
|
|
continue;
|
|
}
|
|
$lc = trim($langRow['languageCode'] ?? '');
|
|
$name = trim($langRow['name'] ?? '');
|
|
if ($lc === '' || $lc === QDB_SOURCE_LANGUAGE) {
|
|
continue;
|
|
}
|
|
$pdo->prepare('INSERT INTO language (languageCode, name) VALUES (:lc, :n)
|
|
ON CONFLICT(languageCode) DO UPDATE SET name = excluded.name')
|
|
->execute([':lc' => $lc, ':n' => $name]);
|
|
}
|
|
|
|
$validTypes = ['question', 'answer_option', 'string', 'app_string'];
|
|
$imported = 0;
|
|
$skipped = 0;
|
|
|
|
foreach ($entries as $entry) {
|
|
if (!is_array($entry)) {
|
|
$skipped++;
|
|
continue;
|
|
}
|
|
$type = $entry['type'] ?? '';
|
|
$entityId = trim((string)($entry['entityId'] ?? ''));
|
|
if (!in_array($type, $validTypes, true) || $entityId === '') {
|
|
$skipped++;
|
|
continue;
|
|
}
|
|
if (!qdb_translation_entity_exists($pdo, $type, $entityId)) {
|
|
$skipped++;
|
|
continue;
|
|
}
|
|
$map = qdb_normalize_bundle_translations($entry['translations'] ?? []);
|
|
if ($map) {
|
|
qdb_apply_translation_map($pdo, $type, $entityId, $map);
|
|
$imported++;
|
|
} else {
|
|
$skipped++;
|
|
}
|
|
}
|
|
|
|
return [
|
|
'imported' => $imported,
|
|
'skipped' => $skipped,
|
|
'total' => count($entries),
|
|
];
|
|
}
|
|
|
|
/**
|
|
* Remove translation rows from the database.
|
|
* By default keeps German source language rows and the de language entry.
|
|
*/
|
|
function qdb_delete_all_translations(PDO $pdo, bool $keepSourceLanguage = true): array {
|
|
if ($keepSourceLanguage) {
|
|
$de = QDB_SOURCE_LANGUAGE;
|
|
$count = static function (PDO $pdo, string $table) use ($de): int {
|
|
$stmt = $pdo->prepare("SELECT COUNT(*) FROM {$table} WHERE languageCode != :de");
|
|
$stmt->execute([':de' => $de]);
|
|
return (int)$stmt->fetchColumn();
|
|
};
|
|
$questionCount = $count($pdo, 'question_translation');
|
|
$answerCount = $count($pdo, 'answer_option_translation');
|
|
$stringCount = $count($pdo, 'string_translation');
|
|
$langStmt = $pdo->prepare('SELECT COUNT(*) FROM language WHERE languageCode != :de');
|
|
$langStmt->execute([':de' => $de]);
|
|
$languageCount = (int)$langStmt->fetchColumn();
|
|
|
|
$pdo->prepare('DELETE FROM question_translation WHERE languageCode != :de')->execute([':de' => $de]);
|
|
$pdo->prepare('DELETE FROM answer_option_translation WHERE languageCode != :de')->execute([':de' => $de]);
|
|
$pdo->prepare('DELETE FROM string_translation WHERE languageCode != :de')->execute([':de' => $de]);
|
|
$pdo->prepare('DELETE FROM language WHERE languageCode != :de')->execute([':de' => $de]);
|
|
} else {
|
|
$questionCount = (int)$pdo->query('SELECT COUNT(*) FROM question_translation')->fetchColumn();
|
|
$answerCount = (int)$pdo->query('SELECT COUNT(*) FROM answer_option_translation')->fetchColumn();
|
|
$stringCount = (int)$pdo->query('SELECT COUNT(*) FROM string_translation')->fetchColumn();
|
|
$languageCount = (int)$pdo->query('SELECT COUNT(*) FROM language')->fetchColumn();
|
|
|
|
$pdo->exec('DELETE FROM question_translation');
|
|
$pdo->exec('DELETE FROM answer_option_translation');
|
|
$pdo->exec('DELETE FROM string_translation');
|
|
$pdo->exec('DELETE FROM language');
|
|
}
|
|
|
|
qdb_ensure_source_language($pdo);
|
|
|
|
return [
|
|
'question' => $questionCount,
|
|
'answer_option' => $answerCount,
|
|
'string' => $stringCount,
|
|
'languages' => $languageCount,
|
|
'keptSource' => $keepSourceLanguage,
|
|
];
|
|
}
|
|
|
|
/** Import a full export bundle. */
|
|
function qdb_import_questionnaires_bundle(PDO $pdo, array $bundle, bool $replaceIfExists = false): array {
|
|
$items = $bundle['questionnaires'] ?? [];
|
|
if (!is_array($items) || !$items) {
|
|
json_error('MISSING_FIELDS', 'bundle.questionnaires array is required', 400);
|
|
}
|
|
foreach ($bundle['appStringTranslations'] ?? [] as $st) {
|
|
if (!is_array($st)) {
|
|
continue;
|
|
}
|
|
$sk = trim($st['stringKey'] ?? '');
|
|
if ($sk === '') {
|
|
continue;
|
|
}
|
|
$map = qdb_normalize_bundle_translations($st['translations'] ?? []);
|
|
if (!$map) {
|
|
continue;
|
|
}
|
|
if (isset($map[QDB_SOURCE_LANGUAGE])) {
|
|
qdb_set_app_string_german_label($pdo, $sk, $map[QDB_SOURCE_LANGUAGE]);
|
|
unset($map[QDB_SOURCE_LANGUAGE]);
|
|
}
|
|
if ($map) {
|
|
qdb_apply_translation_map($pdo, 'app_string', $sk, $map);
|
|
}
|
|
}
|
|
$results = [];
|
|
foreach ($items as $item) {
|
|
$results[] = qdb_import_questionnaire_bundle($pdo, $item, $replaceIfExists);
|
|
}
|
|
if (!empty($bundle['scoringProfiles']) && is_array($bundle['scoringProfiles'])) {
|
|
qdb_import_scoring_profiles_bundle($pdo, $bundle['scoringProfiles'], $replaceIfExists);
|
|
}
|
|
return [
|
|
'imported' => count($results),
|
|
'results' => $results,
|
|
];
|
|
}
|
|
|
|
/** Import scoring profiles from a questionnaire bundle. */
|
|
function qdb_import_scoring_profiles_bundle(PDO $pdo, array $profiles, bool $replaceIfExists = false): void {
|
|
require_once __DIR__ . '/lib/scoring.php';
|
|
foreach ($profiles as $profile) {
|
|
if (!is_array($profile)) {
|
|
continue;
|
|
}
|
|
$wantedId = trim($profile['profileID'] ?? '');
|
|
$name = trim($profile['name'] ?? '');
|
|
if ($name === '') {
|
|
continue;
|
|
}
|
|
$exists = false;
|
|
if ($wantedId !== '') {
|
|
$chk = $pdo->prepare('SELECT 1 FROM scoring_profile WHERE profileID = :id');
|
|
$chk->execute([':id' => $wantedId]);
|
|
$exists = (bool)$chk->fetch();
|
|
}
|
|
if ($exists && $replaceIfExists) {
|
|
$pdo->prepare('DELETE FROM scoring_profile WHERE profileID = :id')->execute([':id' => $wantedId]);
|
|
$profileID = $wantedId;
|
|
} elseif ($exists) {
|
|
$profileID = bin2hex(random_bytes(16));
|
|
} else {
|
|
$profileID = $wantedId !== '' ? $wantedId : bin2hex(random_bytes(16));
|
|
}
|
|
$now = time();
|
|
$pdo->prepare(
|
|
'INSERT INTO scoring_profile (profileID, name, description, isActive,
|
|
greenMin, greenMax, yellowMin, yellowMax, redMin, createdAt, updatedAt)
|
|
VALUES (:id, :name, :desc, :act, :gmin, :gmax, :ymin, :ymax, :rmin, :ca, :ua)'
|
|
)->execute([
|
|
':id' => $profileID,
|
|
':name' => $name,
|
|
':desc' => trim($profile['description'] ?? ''),
|
|
':act' => !empty($profile['isActive']) ? 1 : 0,
|
|
':gmin' => (int)($profile['greenMin'] ?? 0),
|
|
':gmax' => (int)($profile['greenMax'] ?? 12),
|
|
':ymin' => (int)($profile['yellowMin'] ?? ((int)($profile['greenMax'] ?? 12) + 1)),
|
|
':ymax' => (int)($profile['yellowMax'] ?? 36),
|
|
':rmin' => (int)($profile['redMin'] ?? ((int)($profile['yellowMax'] ?? 36) + 1)),
|
|
':ca' => (int)($profile['createdAt'] ?? $now),
|
|
':ua' => (int)($profile['updatedAt'] ?? $now),
|
|
]);
|
|
$members = [];
|
|
foreach ($profile['questionnaires'] ?? [] as $idx => $row) {
|
|
if (!is_array($row)) {
|
|
continue;
|
|
}
|
|
$qnID = trim((string)($row['questionnaireID'] ?? ''));
|
|
if ($qnID === '') {
|
|
continue;
|
|
}
|
|
$chk = $pdo->prepare('SELECT 1 FROM questionnaire WHERE questionnaireID = :id');
|
|
$chk->execute([':id' => $qnID]);
|
|
if (!$chk->fetch()) {
|
|
continue;
|
|
}
|
|
$members[] = [
|
|
'questionnaireID' => $qnID,
|
|
'weight' => (float)($row['weight'] ?? 1.0),
|
|
'orderIndex' => (int)($row['orderIndex'] ?? $idx),
|
|
];
|
|
}
|
|
if ($members !== []) {
|
|
qdb_sync_profile_questionnaire_members($pdo, $profileID, $members);
|
|
}
|
|
}
|
|
}
|
|
|
|
function qdb_sync_profile_questionnaire_members(PDO $pdo, string $profileID, array $members): void {
|
|
$pdo->prepare('DELETE FROM scoring_profile_questionnaire WHERE profileID = :pid')
|
|
->execute([':pid' => $profileID]);
|
|
$ins = $pdo->prepare(
|
|
'INSERT INTO scoring_profile_questionnaire (profileID, questionnaireID, weight, orderIndex)
|
|
VALUES (:pid, :qn, :w, :o)'
|
|
);
|
|
foreach ($members as $m) {
|
|
$ins->execute([
|
|
':pid' => $profileID,
|
|
':qn' => $m['questionnaireID'],
|
|
':w' => $m['weight'],
|
|
':o' => $m['orderIndex'],
|
|
]);
|
|
}
|
|
}
|
|
|
|
// --- AES-256-CBC: IV(16) + CIPHERTEXT ---
|
|
function aes256_cbc_encrypt_bytes(string $plain, string $key): string {
|
|
$key = str_pad(substr($key, 0, 32), 32, "\0");
|
|
$iv = random_bytes(16);
|
|
$cipher = openssl_encrypt($plain, 'AES-256-CBC', $key, OPENSSL_RAW_DATA, $iv);
|
|
if ($cipher === false) throw new Exception('openssl_encrypt failed');
|
|
return $iv . $cipher;
|
|
}
|
|
function aes256_cbc_decrypt_bytes(string $data, string $key): string {
|
|
if (strlen($data) < 16) throw new Exception('cipher too short');
|
|
$key = str_pad(substr($key, 0, 32), 32, "\0");
|
|
$iv = substr($data, 0, 16);
|
|
$ct = substr($data, 16);
|
|
$plain = openssl_decrypt($ct, 'AES-256-CBC', $key, OPENSSL_RAW_DATA, $iv);
|
|
if ($plain === false) throw new Exception('openssl_decrypt failed');
|
|
return $plain;
|
|
}
|