619 lines
21 KiB
TypeScript
619 lines
21 KiB
TypeScript
|
import assert from 'node:assert/strict';
|
||
|
|
import { after, before, describe, it } from 'node:test';
|
||
|
|
import { randomUUID } from 'node:crypto';
|
||
|
|
import ExcelJS from 'exceljs';
|
||
|
|
import {
|
||
|
|
AssignmentDayStatus,
|
||
|
|
AssignmentType,
|
||
|
|
UserAssignmentStatus,
|
||
|
|
UserRole,
|
||
|
|
} from '@prisma/client';
|
||
|
|
import { prisma } from '../src/lib/prisma.js';
|
||
|
|
import {
|
||
|
|
ASSIGNMENTS_EXPORT_CONTENT_TYPE,
|
||
|
|
ASSIGNMENTS_EXPORT_MAX_ROWS,
|
||
|
|
ASSIGNMENTS_EXPORT_SHEET_ITEMS,
|
||
|
|
ASSIGNMENTS_EXPORT_SHEET_SUMMARY,
|
||
|
|
buildAssignmentsExportFilename,
|
||
|
|
buildAssignmentsWorkbook,
|
||
|
|
escapeExcelString,
|
||
|
|
} from '../src/modules/admin/assignmentsExport.js';
|
||
|
|
import { AUDIT_ACTIONS } from '../src/modules/admin/auditActions.js';
|
||
|
|
import {
|
||
|
|
assertNoSecrets,
|
||
|
|
authHeader,
|
||
|
|
createTestApp,
|
||
|
|
isDatabaseAvailable,
|
||
|
|
requestJson,
|
||
|
|
} from './helpers.js';
|
||
|
|
|
||
|
|
const app = createTestApp();
|
||
|
|
const createdUserIds: bigint[] = [];
|
||
|
|
const createdDefinitionIds: bigint[] = [];
|
||
|
|
|
||
|
|
async function trackUser(externalId: string | undefined) {
|
||
|
|
if (!externalId) return;
|
||
|
|
const user = await prisma.user.findUnique({ where: { externalId } });
|
||
|
|
if (user) createdUserIds.push(user.id);
|
||
|
|
}
|
||
|
|
|
||
|
|
async function register(email: string, password = 'password12') {
|
||
|
|
const result = await requestJson(app, '/auth/register', {
|
||
|
|
method: 'POST',
|
||
|
|
body: JSON.stringify({ email, password }),
|
||
|
|
});
|
||
|
|
await trackUser((result.body.data?.user as { id?: string } | undefined)?.id);
|
||
|
|
return result;
|
||
|
|
}
|
||
|
|
|
||
|
|
async function promoteAndLogin(email: string): Promise<{ token: string; userId: bigint }> {
|
||
|
|
const registered = await register(email);
|
||
|
|
assert.equal(registered.status, 201);
|
||
|
|
const externalId = (registered.body.data?.user as { id: string }).id;
|
||
|
|
const user = await prisma.user.findUniqueOrThrow({ where: { externalId } });
|
||
|
|
await prisma.user.update({ where: { id: user.id }, data: { role: UserRole.admin } });
|
||
|
|
const login = await requestJson(app, '/auth/login', {
|
||
|
|
method: 'POST',
|
||
|
|
body: JSON.stringify({ email, password: 'password12' }),
|
||
|
|
});
|
||
|
|
assert.equal(login.status, 200);
|
||
|
|
return {
|
||
|
|
token: login.body.data?.sessionToken as string,
|
||
|
|
userId: user.id,
|
||
|
|
};
|
||
|
|
}
|
||
|
|
|
||
|
|
async function ensureCourseSession(order: number, title: string) {
|
||
|
|
const existing = await prisma.courseSession.findUnique({ where: { order } });
|
||
|
|
if (existing) {
|
||
|
|
return prisma.courseSession.update({
|
||
|
|
where: { order },
|
||
|
|
data: { title },
|
||
|
|
});
|
||
|
|
}
|
||
|
|
return prisma.courseSession.create({
|
||
|
|
data: {
|
||
|
|
sessionKey: `admin-export-test-${order}-${Date.now()}`,
|
||
|
|
order,
|
||
|
|
title,
|
||
|
|
description: 'admin export test',
|
||
|
|
estimatedDurationMinutes: 10,
|
||
|
|
blocks: [],
|
||
|
|
},
|
||
|
|
});
|
||
|
|
}
|
||
|
|
|
||
|
|
async function createDefinition(input: {
|
||
|
|
key: string;
|
||
|
|
sessionOrder: number;
|
||
|
|
title: string;
|
||
|
|
assignmentType?: AssignmentType;
|
||
|
|
expectedDurationDays?: number | null;
|
||
|
|
}) {
|
||
|
|
await ensureCourseSession(input.sessionOrder, `جلسه export ${input.sessionOrder}`);
|
||
|
|
const def = await prisma.assignmentDefinition.create({
|
||
|
|
data: {
|
||
|
|
key: input.key,
|
||
|
|
sessionOrder: input.sessionOrder,
|
||
|
|
title: input.title,
|
||
|
|
description: 'desc',
|
||
|
|
instructions: 'instr',
|
||
|
|
assignmentType: input.assignmentType ?? AssignmentType.DAILY,
|
||
|
|
expectedDurationDays:
|
||
|
|
input.expectedDurationDays !== undefined ? input.expectedDurationDays : 4,
|
||
|
|
displayOrder: input.sessionOrder,
|
||
|
|
},
|
||
|
|
});
|
||
|
|
createdDefinitionIds.push(def.id);
|
||
|
|
return def;
|
||
|
|
}
|
||
|
|
|
||
|
|
async function requestBinary(
|
||
|
|
path: string,
|
||
|
|
init: RequestInit = {},
|
||
|
|
): Promise<{
|
||
|
|
status: number;
|
||
|
|
headers: Headers;
|
||
|
|
buffer: Buffer;
|
||
|
|
textPreview: string;
|
||
|
|
}> {
|
||
|
|
const headers = new Headers(init.headers);
|
||
|
|
const response = await app.fetch(new Request(`http://localhost${path}`, { ...init, headers }));
|
||
|
|
const arrayBuffer = await response.arrayBuffer();
|
||
|
|
const buffer = Buffer.from(arrayBuffer);
|
||
|
|
return {
|
||
|
|
status: response.status,
|
||
|
|
headers: response.headers,
|
||
|
|
buffer,
|
||
|
|
textPreview: buffer.subarray(0, Math.min(buffer.length, 400)).toString('utf8'),
|
||
|
|
};
|
||
|
|
}
|
||
|
|
|
||
|
|
async function loadWorkbook(buffer: Buffer) {
|
||
|
|
const workbook = new ExcelJS.Workbook();
|
||
|
|
await workbook.xlsx.load(buffer);
|
||
|
|
return workbook;
|
||
|
|
}
|
||
|
|
|
||
|
|
function summaryMap(sheet: ExcelJS.Worksheet): Map<string, string | number | boolean | null> {
|
||
|
|
const map = new Map<string, string | number | boolean | null>();
|
||
|
|
sheet.eachRow((row, rowNumber) => {
|
||
|
|
if (rowNumber === 1) return;
|
||
|
|
const field = String(row.getCell(1).value ?? '');
|
||
|
|
const raw = row.getCell(2).value;
|
||
|
|
if (raw == null) {
|
||
|
|
map.set(field, null);
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
if (typeof raw === 'object' && 'text' in raw) {
|
||
|
|
map.set(field, String((raw as { text: string }).text));
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
if (typeof raw === 'object' && 'result' in raw) {
|
||
|
|
map.set(field, String((raw as { result: unknown }).result));
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
map.set(field, raw as string | number | boolean);
|
||
|
|
});
|
||
|
|
return map;
|
||
|
|
}
|
||
|
|
|
||
|
|
describe('admin assignments export', () => {
|
||
|
|
let dbAvailable = false;
|
||
|
|
|
||
|
|
before(async () => {
|
||
|
|
dbAvailable = await isDatabaseAvailable();
|
||
|
|
});
|
||
|
|
|
||
|
|
after(async () => {
|
||
|
|
if (!dbAvailable) return;
|
||
|
|
if (createdUserIds.length > 0) {
|
||
|
|
await prisma.auditLog.deleteMany({
|
||
|
|
where: {
|
||
|
|
OR: [
|
||
|
|
{ actorUserId: { in: createdUserIds } },
|
||
|
|
{ targetUserId: { in: createdUserIds } },
|
||
|
|
],
|
||
|
|
},
|
||
|
|
});
|
||
|
|
await prisma.user.deleteMany({ where: { id: { in: createdUserIds } } });
|
||
|
|
}
|
||
|
|
if (createdDefinitionIds.length > 0) {
|
||
|
|
await prisma.assignmentDefinition.deleteMany({
|
||
|
|
where: { id: { in: createdDefinitionIds } },
|
||
|
|
});
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
it('rejects workbook build when assignment rows exceed the export cap', async () => {
|
||
|
|
assert.equal(ASSIGNMENTS_EXPORT_MAX_ROWS, 500);
|
||
|
|
const oversized = {
|
||
|
|
user: { id: randomUUID(), email: null },
|
||
|
|
learningProgress: {
|
||
|
|
currentStep: null,
|
||
|
|
trainingStarted: false,
|
||
|
|
trainingCompleted: false,
|
||
|
|
courseCompleted: false,
|
||
|
|
totalLessons: 11,
|
||
|
|
completedLessons: 0,
|
||
|
|
progressPercent: 0,
|
||
|
|
},
|
||
|
|
summary: {
|
||
|
|
total: 501,
|
||
|
|
notStarted: 501,
|
||
|
|
opened: 0,
|
||
|
|
inProgress: 0,
|
||
|
|
completed: 0,
|
||
|
|
pending: 501,
|
||
|
|
progressPercent: null,
|
||
|
|
},
|
||
|
|
items: Array.from({ length: 501 }, (_, index) => ({
|
||
|
|
assignmentId: String(index),
|
||
|
|
definitionId: String(index),
|
||
|
|
definitionKey: `k-${index}`,
|
||
|
|
definitionTitle: `t-${index}`,
|
||
|
|
assignmentType: 'ONE_TIME',
|
||
|
|
programId: null,
|
||
|
|
programTitle: null,
|
||
|
|
sessionId: null,
|
||
|
|
sessionOrder: 1,
|
||
|
|
sessionTitle: null,
|
||
|
|
status: UserAssignmentStatus.NOT_STARTED,
|
||
|
|
assignedAt: new Date().toISOString(),
|
||
|
|
startedAt: null,
|
||
|
|
completedAt: null,
|
||
|
|
lastActivityAt: null,
|
||
|
|
doneDayCount: 0,
|
||
|
|
expectedDurationDays: null,
|
||
|
|
progressPercent: null,
|
||
|
|
})),
|
||
|
|
};
|
||
|
|
|
||
|
|
await assert.rejects(
|
||
|
|
() => buildAssignmentsWorkbook(oversized),
|
||
|
|
(error: unknown) =>
|
||
|
|
error instanceof Error &&
|
||
|
|
error.name === 'ValidationError' &&
|
||
|
|
/Too many assignments/i.test(error.message),
|
||
|
|
);
|
||
|
|
});
|
||
|
|
|
||
|
|
it('sanitizes export filename and keeps workbook sheet names fixed', async () => {
|
||
|
|
assert.equal(
|
||
|
|
buildAssignmentsExportFilename('abc/../x\ny"z'),
|
||
|
|
'noya-assignments-abc-x-y-z.xlsx',
|
||
|
|
);
|
||
|
|
assert.equal(
|
||
|
|
buildAssignmentsExportFilename('../../../etc/passwd'),
|
||
|
|
'noya-assignments-etc-passwd.xlsx',
|
||
|
|
);
|
||
|
|
|
||
|
|
const empty = await buildAssignmentsWorkbook({
|
||
|
|
user: { id: '11111111-1111-1111-1111-111111111111', email: null },
|
||
|
|
learningProgress: {
|
||
|
|
currentStep: null,
|
||
|
|
trainingStarted: false,
|
||
|
|
trainingCompleted: false,
|
||
|
|
courseCompleted: false,
|
||
|
|
totalLessons: 11,
|
||
|
|
completedLessons: 0,
|
||
|
|
progressPercent: 0,
|
||
|
|
},
|
||
|
|
summary: {
|
||
|
|
total: 0,
|
||
|
|
notStarted: 0,
|
||
|
|
opened: 0,
|
||
|
|
inProgress: 0,
|
||
|
|
completed: 0,
|
||
|
|
pending: 0,
|
||
|
|
progressPercent: null,
|
||
|
|
},
|
||
|
|
items: [],
|
||
|
|
});
|
||
|
|
const workbook = await loadWorkbook(empty.buffer);
|
||
|
|
assert.equal(workbook.worksheets.length, 2);
|
||
|
|
assert.equal(workbook.worksheets[0]!.name, ASSIGNMENTS_EXPORT_SHEET_SUMMARY);
|
||
|
|
assert.equal(workbook.worksheets[1]!.name, ASSIGNMENTS_EXPORT_SHEET_ITEMS);
|
||
|
|
assert.match(empty.filename, /^noya-assignments-[a-zA-Z0-9_-]+\.xlsx$/);
|
||
|
|
});
|
||
|
|
|
||
|
|
it('returns 401 without session', async (t) => {
|
||
|
|
if (!dbAvailable) {
|
||
|
|
t.skip('MySQL is not running');
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
const { status, textPreview } = await requestBinary(
|
||
|
|
`/admin/users/${randomUUID()}/assignments/export`,
|
||
|
|
);
|
||
|
|
assert.equal(status, 401);
|
||
|
|
assertNoSecrets(textPreview);
|
||
|
|
});
|
||
|
|
|
||
|
|
it('returns 403 for a normal user', async (t) => {
|
||
|
|
if (!dbAvailable) {
|
||
|
|
t.skip('MySQL is not running');
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
const registered = await register(`export-user-${Date.now()}@example.com`);
|
||
|
|
const token = registered.body.data?.sessionToken as string;
|
||
|
|
const id = (registered.body.data?.user as { id: string }).id;
|
||
|
|
const { status, textPreview } = await requestBinary(`/admin/users/${id}/assignments/export`, {
|
||
|
|
headers: authHeader(token),
|
||
|
|
});
|
||
|
|
assert.equal(status, 403);
|
||
|
|
assertNoSecrets(textPreview);
|
||
|
|
});
|
||
|
|
|
||
|
|
it('returns 422 for invalid externalId', async (t) => {
|
||
|
|
if (!dbAvailable) {
|
||
|
|
t.skip('MySQL is not running');
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
const { token } = await promoteAndLogin(`export-invalid-${Date.now()}@example.com`);
|
||
|
|
const { status, textPreview } = await requestBinary('/admin/users/not-a-uuid/assignments/export', {
|
||
|
|
headers: authHeader(token),
|
||
|
|
});
|
||
|
|
assert.equal(status, 422);
|
||
|
|
assertNoSecrets(textPreview);
|
||
|
|
});
|
||
|
|
|
||
|
|
it('returns 404 for missing user', async (t) => {
|
||
|
|
if (!dbAvailable) {
|
||
|
|
t.skip('MySQL is not running');
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
const { token } = await promoteAndLogin(`export-missing-${Date.now()}@example.com`);
|
||
|
|
const { status, textPreview } = await requestBinary(
|
||
|
|
`/admin/users/${randomUUID()}/assignments/export`,
|
||
|
|
{ headers: authHeader(token) },
|
||
|
|
);
|
||
|
|
assert.equal(status, 404);
|
||
|
|
assertNoSecrets(textPreview);
|
||
|
|
});
|
||
|
|
|
||
|
|
it('exports empty assignments workbook with headers and audit', async (t) => {
|
||
|
|
if (!dbAvailable) {
|
||
|
|
t.skip('MySQL is not running');
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
const { token, userId: adminId } = await promoteAndLogin(
|
||
|
|
`export-empty-admin-${Date.now()}@example.com`,
|
||
|
|
);
|
||
|
|
const registered = await register(`export-empty-target-${Date.now()}@example.com`);
|
||
|
|
const externalId = (registered.body.data?.user as { id: string }).id;
|
||
|
|
|
||
|
|
const beforeCount = await prisma.userAssignment.count({
|
||
|
|
where: { user: { externalId } },
|
||
|
|
});
|
||
|
|
|
||
|
|
const { status, headers, buffer, textPreview } = await requestBinary(
|
||
|
|
`/admin/users/${externalId}/assignments/export`,
|
||
|
|
{ headers: authHeader(token) },
|
||
|
|
);
|
||
|
|
assert.equal(status, 200);
|
||
|
|
assert.equal(headers.get('content-type'), ASSIGNMENTS_EXPORT_CONTENT_TYPE);
|
||
|
|
const disposition = headers.get('content-disposition') ?? '';
|
||
|
|
assert.match(disposition, /^attachment; filename="noya-assignments-[a-zA-Z0-9_-]+\.xlsx"$/);
|
||
|
|
assert.equal(!/[\\/\n\r"]/.test(disposition.replace(/^attachment; filename="|"$/g, '')), true);
|
||
|
|
assert.ok(buffer.length > 100);
|
||
|
|
assert.equal(!/passwordHash|tokenHash|draftData|reportText/i.test(textPreview), true);
|
||
|
|
|
||
|
|
const workbook = await loadWorkbook(buffer);
|
||
|
|
assert.ok(workbook.getWorksheet(ASSIGNMENTS_EXPORT_SHEET_SUMMARY));
|
||
|
|
assert.ok(workbook.getWorksheet(ASSIGNMENTS_EXPORT_SHEET_ITEMS));
|
||
|
|
|
||
|
|
const summary = summaryMap(workbook.getWorksheet(ASSIGNMENTS_EXPORT_SHEET_SUMMARY)!);
|
||
|
|
assert.equal(summary.get('External ID'), externalId);
|
||
|
|
assert.equal(summary.get('Total Assignments'), 0);
|
||
|
|
assert.equal(summary.get('Completed Assignments'), 0);
|
||
|
|
|
||
|
|
const itemsSheet = workbook.getWorksheet(ASSIGNMENTS_EXPORT_SHEET_ITEMS)!;
|
||
|
|
assert.equal(itemsSheet.rowCount, 1); // header only
|
||
|
|
assert.equal(String(itemsSheet.getRow(1).getCell(1).value), 'Assignment ID');
|
||
|
|
assert.equal(String(itemsSheet.getRow(1).getCell(16).value), 'Progress Percent');
|
||
|
|
|
||
|
|
const afterCount = await prisma.userAssignment.count({
|
||
|
|
where: { user: { externalId } },
|
||
|
|
});
|
||
|
|
assert.equal(afterCount, beforeCount);
|
||
|
|
|
||
|
|
const audit = await prisma.auditLog.findFirst({
|
||
|
|
where: {
|
||
|
|
actorUserId: adminId,
|
||
|
|
action: AUDIT_ACTIONS.ADMIN_EXPORT_ASSIGNMENTS,
|
||
|
|
targetId: externalId,
|
||
|
|
},
|
||
|
|
orderBy: { createdAt: 'desc' },
|
||
|
|
});
|
||
|
|
assert.ok(audit);
|
||
|
|
const meta = audit!.metadata as Record<string, unknown>;
|
||
|
|
assert.equal(meta.view, 'assignments_export');
|
||
|
|
assert.equal(meta.format, 'xlsx');
|
||
|
|
assert.equal(meta.rowCount, 0);
|
||
|
|
assert.equal(meta.sheetCount, 2);
|
||
|
|
assert.equal(meta.externalId, externalId);
|
||
|
|
const metaJson = JSON.stringify(meta);
|
||
|
|
assert.equal(!/draftData|reportText|password|workbook/i.test(metaJson), true);
|
||
|
|
});
|
||
|
|
|
||
|
|
it('exports only target user rows with safe values and formula injection protection', async (t) => {
|
||
|
|
if (!dbAvailable) {
|
||
|
|
t.skip('MySQL is not running');
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
const stamp = Date.now();
|
||
|
|
const { token } = await promoteAndLogin(`export-list-admin-${stamp}@example.com`);
|
||
|
|
const targetReg = await register(`export-list-target-${stamp}@example.com`);
|
||
|
|
const otherReg = await register(`export-list-other-${stamp}@example.com`);
|
||
|
|
const targetId = (targetReg.body.data?.user as { id: string }).id;
|
||
|
|
const otherId = (otherReg.body.data?.user as { id: string }).id;
|
||
|
|
const targetUser = await prisma.user.findUniqueOrThrow({ where: { externalId: targetId } });
|
||
|
|
const otherUser = await prisma.user.findUniqueOrThrow({ where: { externalId: otherId } });
|
||
|
|
|
||
|
|
const dangerousTitle = '=1+2';
|
||
|
|
const defA = await createDefinition({
|
||
|
|
key: `export-a-${stamp}`,
|
||
|
|
sessionOrder: 6,
|
||
|
|
title: dangerousTitle,
|
||
|
|
expectedDurationDays: 4,
|
||
|
|
});
|
||
|
|
const defB = await createDefinition({
|
||
|
|
key: `+cmd-${stamp}`,
|
||
|
|
sessionOrder: 7,
|
||
|
|
title: 'Safe title',
|
||
|
|
assignmentType: AssignmentType.ONE_TIME,
|
||
|
|
expectedDurationDays: null,
|
||
|
|
});
|
||
|
|
const defOther = await createDefinition({
|
||
|
|
key: `export-other-${stamp}`,
|
||
|
|
sessionOrder: 8,
|
||
|
|
title: 'Other user assignment',
|
||
|
|
});
|
||
|
|
|
||
|
|
const activatedAt = new Date('2026-03-01T10:00:00.000Z');
|
||
|
|
const ua = await prisma.userAssignment.create({
|
||
|
|
data: {
|
||
|
|
userId: targetUser.id,
|
||
|
|
definitionId: defA.id,
|
||
|
|
status: UserAssignmentStatus.COMPLETED,
|
||
|
|
activatedAt,
|
||
|
|
startedAt: new Date('2026-03-02T10:00:00.000Z'),
|
||
|
|
completedAt: new Date('2026-03-03T10:00:00.000Z'),
|
||
|
|
},
|
||
|
|
});
|
||
|
|
await prisma.assignmentDailyEntry.create({
|
||
|
|
data: {
|
||
|
|
userAssignmentId: ua.id,
|
||
|
|
userId: targetUser.id,
|
||
|
|
entryDate: new Date('2026-03-02'),
|
||
|
|
dayStatus: AssignmentDayStatus.done,
|
||
|
|
responseData: { secret: 'should-not-export' },
|
||
|
|
},
|
||
|
|
});
|
||
|
|
await prisma.userAssignment.create({
|
||
|
|
data: {
|
||
|
|
userId: targetUser.id,
|
||
|
|
definitionId: defB.id,
|
||
|
|
status: UserAssignmentStatus.ACTIVE,
|
||
|
|
activatedAt: new Date('2026-03-04T10:00:00.000Z'),
|
||
|
|
},
|
||
|
|
});
|
||
|
|
await prisma.userAssignment.create({
|
||
|
|
data: {
|
||
|
|
userId: otherUser.id,
|
||
|
|
definitionId: defOther.id,
|
||
|
|
status: UserAssignmentStatus.IN_PROGRESS,
|
||
|
|
},
|
||
|
|
});
|
||
|
|
|
||
|
|
await prisma.learningJourney.upsert({
|
||
|
|
where: { userId: targetUser.id },
|
||
|
|
create: {
|
||
|
|
userId: targetUser.id,
|
||
|
|
preTestCompletedAt: new Date(),
|
||
|
|
trainingStartedAt: new Date(),
|
||
|
|
},
|
||
|
|
update: {
|
||
|
|
preTestCompletedAt: new Date(),
|
||
|
|
trainingStartedAt: new Date(),
|
||
|
|
trainingCompletedAt: null,
|
||
|
|
postTestCompletedAt: null,
|
||
|
|
},
|
||
|
|
});
|
||
|
|
|
||
|
|
const beforeUa = await prisma.userAssignment.findUniqueOrThrow({ where: { id: ua.id } });
|
||
|
|
|
||
|
|
const { status, headers, buffer, textPreview } = await requestBinary(
|
||
|
|
`/admin/users/${targetId}/assignments/export?sortBy=sessionOrder&sortDir=asc`,
|
||
|
|
{ headers: authHeader(token) },
|
||
|
|
);
|
||
|
|
assert.equal(status, 200);
|
||
|
|
assert.equal(headers.get('content-type'), ASSIGNMENTS_EXPORT_CONTENT_TYPE);
|
||
|
|
assert.match(
|
||
|
|
headers.get('content-disposition') ?? '',
|
||
|
|
/attachment; filename="noya-assignments-/,
|
||
|
|
);
|
||
|
|
assert.equal(!/should-not-export|passwordHash|draftData|reportText/i.test(textPreview), true);
|
||
|
|
|
||
|
|
const workbook = await loadWorkbook(buffer);
|
||
|
|
const summary = summaryMap(workbook.getWorksheet(ASSIGNMENTS_EXPORT_SHEET_SUMMARY)!);
|
||
|
|
assert.equal(summary.get('External ID'), targetId);
|
||
|
|
assert.equal(summary.get('Total Assignments'), 2);
|
||
|
|
assert.equal(summary.get('Completed Assignments'), 1);
|
||
|
|
assert.equal(summary.get('In Progress Assignments'), 1);
|
||
|
|
assert.equal(summary.get('Current Step'), 'training_in_progress');
|
||
|
|
assert.equal(summary.get('Training Started'), true);
|
||
|
|
assert.equal(summary.get('Course Completed'), false);
|
||
|
|
|
||
|
|
const itemsSheet = workbook.getWorksheet(ASSIGNMENTS_EXPORT_SHEET_ITEMS)!;
|
||
|
|
assert.equal(itemsSheet.rowCount, 3); // header + 2 rows
|
||
|
|
|
||
|
|
const row1 = itemsSheet.getRow(2);
|
||
|
|
const row2 = itemsSheet.getRow(3);
|
||
|
|
const titles = [String(row1.getCell(4).value ?? ''), String(row2.getCell(4).value ?? '')];
|
||
|
|
assert.ok(titles.includes(escapeExcelString(dangerousTitle)));
|
||
|
|
assert.ok(titles.includes('Safe title'));
|
||
|
|
assert.equal(titles.includes('Other user assignment'), false);
|
||
|
|
|
||
|
|
const dangerousCell = titles.find((v) => v.includes('1+2'))!;
|
||
|
|
assert.equal(dangerousCell.startsWith("'"), true);
|
||
|
|
assert.equal(dangerousCell, `'${dangerousTitle}`);
|
||
|
|
|
||
|
|
// ExcelJS must store escaped values as plain text, not formulas.
|
||
|
|
const titleCell = row1.getCell(4).value;
|
||
|
|
assert.equal(typeof titleCell === 'object' && titleCell !== null && 'formula' in titleCell, false);
|
||
|
|
|
||
|
|
const keys = [String(row1.getCell(3).value ?? ''), String(row2.getCell(3).value ?? '')];
|
||
|
|
assert.ok(keys.some((k) => k === escapeExcelString(`+cmd-${stamp}`)));
|
||
|
|
|
||
|
|
const completedRow = [row1, row2].find(
|
||
|
|
(row) => String(row.getCell(9).value) === UserAssignmentStatus.COMPLETED,
|
||
|
|
);
|
||
|
|
assert.ok(completedRow);
|
||
|
|
assert.equal(String(completedRow!.getCell(10).value), activatedAt.toISOString());
|
||
|
|
assert.equal(Number(completedRow!.getCell(14).value), 1);
|
||
|
|
assert.equal(Number(completedRow!.getCell(15).value), 4);
|
||
|
|
assert.equal(Number(completedRow!.getCell(16).value), 25);
|
||
|
|
|
||
|
|
const activeRow = [row1, row2].find(
|
||
|
|
(row) => String(row.getCell(9).value) === UserAssignmentStatus.ACTIVE,
|
||
|
|
);
|
||
|
|
assert.ok(activeRow);
|
||
|
|
assert.equal(activeRow!.getCell(16).value ?? '', '');
|
||
|
|
|
||
|
|
const afterUa = await prisma.userAssignment.findUniqueOrThrow({ where: { id: ua.id } });
|
||
|
|
assert.equal(afterUa.status, beforeUa.status);
|
||
|
|
assert.equal(afterUa.updatedAt.toISOString(), beforeUa.updatedAt.toISOString());
|
||
|
|
assert.equal(afterUa.completedAt?.toISOString(), beforeUa.completedAt?.toISOString());
|
||
|
|
});
|
||
|
|
|
||
|
|
it('returns 422 for more than 500 assignments without successful export audit', async (t) => {
|
||
|
|
if (!dbAvailable) {
|
||
|
|
t.skip('MySQL is not running');
|
||
|
|
return;
|
||
|
|
}
|
||
|
|
const stamp = Date.now();
|
||
|
|
const { token, userId: adminId } = await promoteAndLogin(
|
||
|
|
`export-cap-admin-${stamp}@example.com`,
|
||
|
|
);
|
||
|
|
const registered = await register(`export-cap-target-${stamp}@example.com`);
|
||
|
|
const externalId = (registered.body.data?.user as { id: string }).id;
|
||
|
|
const target = await prisma.user.findUniqueOrThrow({ where: { externalId } });
|
||
|
|
|
||
|
|
await ensureCourseSession(1, 'جلسه cap');
|
||
|
|
const count = ASSIGNMENTS_EXPORT_MAX_ROWS + 1;
|
||
|
|
await prisma.assignmentDefinition.createMany({
|
||
|
|
data: Array.from({ length: count }, (_, index) => ({
|
||
|
|
key: `export-cap-${stamp}-${index}`,
|
||
|
|
sessionOrder: 1,
|
||
|
|
title: `Cap ${index}`,
|
||
|
|
description: 'cap',
|
||
|
|
instructions: 'cap',
|
||
|
|
assignmentType: AssignmentType.ONE_TIME,
|
||
|
|
expectedDurationDays: null,
|
||
|
|
displayOrder: index,
|
||
|
|
})),
|
||
|
|
});
|
||
|
|
const defs = await prisma.assignmentDefinition.findMany({
|
||
|
|
where: { key: { startsWith: `export-cap-${stamp}-` } },
|
||
|
|
select: { id: true },
|
||
|
|
});
|
||
|
|
assert.equal(defs.length, count);
|
||
|
|
createdDefinitionIds.push(...defs.map((row) => row.id));
|
||
|
|
|
||
|
|
await prisma.userAssignment.createMany({
|
||
|
|
data: defs.map((def) => ({
|
||
|
|
userId: target.id,
|
||
|
|
definitionId: def.id,
|
||
|
|
status: UserAssignmentStatus.NOT_STARTED,
|
||
|
|
})),
|
||
|
|
});
|
||
|
|
|
||
|
|
const auditsBefore = await prisma.auditLog.count({
|
||
|
|
where: {
|
||
|
|
actorUserId: adminId,
|
||
|
|
action: AUDIT_ACTIONS.ADMIN_EXPORT_ASSIGNMENTS,
|
||
|
|
targetId: externalId,
|
||
|
|
},
|
||
|
|
});
|
||
|
|
|
||
|
|
const { status, buffer, textPreview } = await requestBinary(
|
||
|
|
`/admin/users/${externalId}/assignments/export`,
|
||
|
|
{ headers: authHeader(token) },
|
||
|
|
);
|
||
|
|
assert.equal(status, 422);
|
||
|
|
assert.ok(buffer.length < 5_000);
|
||
|
|
assert.equal(!buffer.subarray(0, 2).equals(Buffer.from([0x50, 0x4b])), true); // not a zip/xlsx
|
||
|
|
assert.match(textPreview, /Too many assignments|VALIDATION_ERROR/i);
|
||
|
|
|
||
|
|
const auditsAfter = await prisma.auditLog.count({
|
||
|
|
where: {
|
||
|
|
actorUserId: adminId,
|
||
|
|
action: AUDIT_ACTIONS.ADMIN_EXPORT_ASSIGNMENTS,
|
||
|
|
targetId: externalId,
|
||
|
|
},
|
||
|
|
});
|
||
|
|
assert.equal(auditsAfter, auditsBefore);
|
||
|
|
});
|
||
|
|
});
|