File size: 2,570 Bytes
1187856 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | import { getDb } from "@/lib/db";
import { all, get as dbGet } from "@/lib/db/helpers";
import type { Metric, MetricExplanation, MetricScore } from "@/lib/db/schema";
import { localized } from "@/lib/i18n";
export function getMetrics(): Metric[] {
return all<Metric>(getDb().prepare("SELECT * FROM metrics ORDER BY sort_order, id")).map((row) => ({
...row,
displayNameText: localized(row.display_name_en || row.display_name, row.display_name_zh),
shortLabelText: localized(row.short_label_en || row.display_name_en || row.display_name, row.short_label_zh || row.display_name_zh),
descriptionText: localized(row.description_en || row.description || "", row.description_zh),
}));
}
export function getMetricExplanations(): MetricExplanation[] {
return all<MetricExplanation>(getDb().prepare("SELECT * FROM metric_explanations ORDER BY id"));
}
export function latestScoresForSubmissions(
submissionIds: number[]
): Map<number, Record<string, MetricScore>> {
const db = getDb();
if (!submissionIds.length) return new Map();
const placeholders = submissionIds.map(() => "?").join(",");
const rows = db
.prepare(
`SELECT sr.*, m.*
FROM score_records sr JOIN metrics m ON m.id = sr.metric_id
JOIN (SELECT submission_id, metric_id, MAX(id) AS latest_id
FROM score_records WHERE submission_id IN (${placeholders})
GROUP BY submission_id, metric_id) latest ON latest.latest_id = sr.id
ORDER BY m.sort_order`
)
.all(...submissionIds) as Array<Record<string, unknown>>;
const grouped = new Map<number, Record<string, MetricScore>>();
for (const row of rows) {
const sid = row.submission_id as number;
if (!grouped.has(sid)) grouped.set(sid, {});
grouped.get(sid)![row.code as string] = {
code: row.code as string,
displayName: row.display_name as string,
displayNameText: localized(
(row.display_name_en as string | null) || (row.display_name as string),
row.display_name_zh as string | null
),
shortLabelText: localized(
(row.short_label_en as string | null) || (row.display_name_en as string | null) || (row.display_name as string),
(row.short_label_zh as string | null) || (row.display_name_zh as string | null)
),
category: row.category as string,
rawValue: row.raw_value as number | null,
normalizedValue: row.normalized_value as number | null,
percentile: row.percentile as number | null,
recordedAt: row.recorded_at as string,
};
}
return grouped;
}
|