|
|
// 导入学生名单（从Excel）
|
|
|
app.post('/import-students', upload.single('file'), async (req, res) => {
|
|
|
try {
|
|
|
const file = req.file;
|
|
|
const workbook = xlsx.readFile(file.path);
|
|
|
const sheet = workbook.Sheets[workbook.SheetNames[0]];
|
|
|
const students = xlsx.utils.sheet_to_json(sheet); // 解析Excel数据
|
|
|
|
|
|
// 批量插入数据库
|
|
|
for (const student of students) {
|
|
|
await db.execute(
|
|
|
'INSERT INTO students (student_id, name, major) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE name=?, major=?',
|
|
|
[student.学号, student.姓名, student.专业, student.姓名, student.专业]
|
|
|
);
|
|
|
}
|
|
|
res.json({ success: true, message: '导入成功' });
|
|
|
} catch (err) {
|
|
|
res.status(500).json({ success: false, error: err.message });
|
|
|
}
|
|
|
});
|
|
|
|
|
|
// 随机点名（根据积分调整概率：积分越高，概率越低）
|
|
|
app.get('/random-call', async (req, res) => {
|
|
|
try {
|
|
|
// 查询所有学生的积分和学号
|
|
|
const [students] = await db.execute('SELECT student_id, name, score FROM students');
|
|
|
|
|
|
// 计算权重（积分倒数，避免0积分导致权重无穷大）
|
|
|
const totalWeight = students.reduce((sum, s) => sum + (1 / (s.score + 1)), 0);
|
|
|
let random = Math.random() * totalWeight;
|
|
|
let selected;
|
|
|
|
|
|
// 根据权重随机选择学生
|
|
|
for (const s of students) {
|
|
|
const weight = 1 / (s.score + 1);
|
|
|
if (random <= weight) {
|
|
|
selected = s;
|
|
|
break;
|
|
|
}
|
|
|
random -= weight;
|
|
|
}
|
|
|
|
|
|
// 记录点名信息并更新随机点名次数
|
|
|
await db.execute(
|
|
|
'INSERT INTO attendance (student_id, mode) VALUES (?, ?)',
|
|
|
[selected.student_id, 'random']
|
|
|
);
|
|
|
await db.execute(
|
|
|
'UPDATE students SET random_count = random_count + 1 WHERE student_id = ?',
|
|
|
[selected.student_id]
|
|
|
);
|
|
|
|
|
|
res.json({ success: true, student: selected });
|
|
|
} catch (err) {
|
|
|
res.status(500).json({ success: false, error: err.message });
|
|
|
}
|
|
|
});
|
|
|
|
|
|
// 更新学生积分（例如：回答问题后）
|
|
|
app.post('/update-score', async (req, res) => {
|
|
|
const { student_id, answer_score } = req.body;
|
|
|
try {
|
|
|
// 先获取当前积分
|
|
|
const [rows] = await db.execute('SELECT score FROM students WHERE student_id = ?', [student_id]);
|
|
|
const currentScore = rows[0].score;
|
|
|
|
|
|
// 更新积分（出勤+1分已在点名时处理，此处仅处理回答问题得分）
|
|
|
const newScore = currentScore + answer_score;
|
|
|
await db.execute(
|
|
|
'UPDATE students SET score = ? WHERE student_id = ?',
|
|
|
[newScore, student_id]
|
|
|
);
|
|
|
// 同步更新点名记录的回答得分
|
|
|
await db.execute(
|
|
|
'UPDATE attendance SET answer_score = ? WHERE student_id = ? ORDER BY attendance_time DESC LIMIT 1',
|
|
|
[answer_score, student_id]
|
|
|
);
|
|
|
res.json({ success: true, newScore });
|
|
|
} catch (err) {
|
|
|
res.status(500).json({ success: false, error: err.message });
|
|
|
}
|
|
|
});
|
|
|
|
|
|
// 导出积分详单（生成Excel）
|
|
|
app.get('/export-scores', async (req, res) => {
|
|
|
try {
|
|
|
const [students] = await db.execute(
|
|
|
'SELECT student_id, name, major, random_count, score FROM students'
|
|
|
);
|
|
|
const worksheet = xlsx.utils.json_to_sheet(students);
|
|
|
const workbook = xlsx.utils.book_new();
|
|
|
xlsx.utils.book_append_sheet(workbook, worksheet, '积分详单');
|
|
|
const buffer = xlsx.write(workbook, { type: 'buffer', bookType: 'xlsx' });
|
|
|
res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
|
|
res.setHeader('Content-Disposition', 'attachment; filename="scores.xlsx"');
|
|
|
res.send(buffer);
|
|
|
} catch (err) {
|
|
|
res.status(500).json({ success: false, error: err.message });
|
|
|
}
|
|
|
}); |
